Best way to register two foreign keys in a table



  • Possue 3 tables, being: person_fisica, _ and address.

    So much 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 a linked to id of pessoa_fisica and the other id of pessoa_juridica. Thus, if a registration pessoa_fisica or pessoa_juridica be 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 of foreign keys in the tables pessoa_fisica and pessoa_juridica.

    There is some way to treat this without having to create two address tables (one to pessoa_fisica and another to pessoa_juridica)?



  • You have to create in table 2 foreign keys and they can be null, Example of creating tables:

    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 given fake 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 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;

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2