Best way to register two foreign keys in a table

Question

Best way to register two foreign keys in a table

Navigation

#1 by (1 votes)

0

I have 3 tables, which are: personal_person , personal_juridica and address .

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

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

Is there any way to handle this without having to create two address tables (one for pessoa_fisica and one for pessoa_juridica )?

mysql chave-estrangeira

asked by anonymous 04.12.2017 / 18:29

1 answer

Avoiding arrays as values when using array_merge_recursive () help with for loop, with variable approaching 100

score 1 · Accepted Answer

You have to create in the table 2 foreign keys that can be null, Tables create example:

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 fake data tables 1 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 physical and legal persons 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;

But it is not the best form of modeling, the tables physical_persons and juridical people should be only 1 table where there would be referencing with person type because there are many columns that can be the same, and if there were more than one table, you would create other tables tables with this information, but that solves your problem.