Best way to register two foreign keys in a table

Question

I have 3 tables, being them: personal, pesso_juridical and addressee.

Both the tebela pessoa_fisica, as for tebela pessoa_juridica use the table endereco. I would like to add two foreign keys in the table endereço. Being one linked to id of pessoa_fisica and another to id of pessoa_juridica. Thus, in case a record of pessoa_fisica or pessoa_juridica is deleted, the address will also be.

My problem is that Mysql does not allow me to register a record in the address table if there is no reference to foreign keys in the tables pessoa_fisica and pessoa_juridica.

Is there any way to treat this without creating two address tables (one for pessoa_fisica and another to pessoa_juridica)?

Answer 1

You have to create in table 2 foreign keys can be null, Example of table create:

CREATE TABLE IF NOT EXISTS forum.pessoa_fisica (
  id INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(60) NOT NULL,
  cpf VARCHAR(15) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS forum.pessoa_juridica (
  id INT NOT NULL AUTO_INCREMENT,
  razao_social VARCHAR(255) NOT NULL,
  cnpj VARCHAR(30) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB; 



CREATE TABLE IF NOT EXISTS forum.endereco (
  id INT NOT NULL AUTO_INCREMENT,
  id_pessoa_juridica INT NULL,
  id_pessoa_fisica INT NULL,
  rua VARCHAR(255) NOT NULL,
  numero VARCHAR(10) NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_endereco_pessoa_juridica_idx (id_pessoa_juridica ASC),
  INDEX fk_endereco_pessoa_fisica1_idx (id_pessoa_fisica ASC),
  CONSTRAINT fk_endereco_pessoa_juridica
    FOREIGN KEY (id_pessoa_juridica)
    REFERENCES forum.pessoa_juridica (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_endereco_pessoa_fisica1
    FOREIGN KEY (id_pessoa_fisica)
    REFERENCES forum.pessoa_fisica (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Here I will insert in tables 1 fake data for each table

INSERT INTO pessoa_fisica(nome, cpf)VALUES('PessoaFisica', '00000000000');
INSERT INTO pessoa_juridica(razao_social, cnpj)VALUES('PessoaJuridica', '000000000000000');
INSERT INTO endereco(id_pessoa_fisica, rua, numero) VALUES(1,'Rua Pessoa Física', '1');
INSERT INTO endereco(id_pessoa_juridica, rua, numero) VALUES(1,'Rua Pessoa Juridica', '2');

To consult the addresses of the physical people and Uridicas we use the following querys:

/** CONSULTA ENDERECO PESSOA FISICA ID 1*/
SELECT
    pf.nome,
    pf.cpf,
    e.rua,
    e.numero
FROM pessoa_fisica pf
LEFT JOIN endereco e
ON e.id_pessoa_fisica = pf.id
WHERE pf.id = 1;


/** CONSULTA ENDERECO PESSOA JURIDICA ID 1*/
SELECT
    pj.razao_social,
    pj.cnpj,
    e.rua,
    e.numero
FROM pessoa_juridica pj
LEFT JOIN endereco e
ON e.id_pessoa_fisica = pj.id
WHERE pj.id = 1;

However it is not the best form of modeling, the personal and personal tables should be only 1 table where there would be reference with type person because it has many columns that can be equal, and if there was something more in some table, you would create other tables with this information, but it solves your problem.