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.
@Marconi there is different information in person and person. So this option is not feasible for me.
– alan
I would like to get an option where it is not necessary to create two address tables. Pq the address fields are equal for the two tables. So I don’t know if duplicating this table would be the best option!
– alan
I found interesting your question, here’s a great step of how to model your tables. Data Modeling - Part 06 (Generalizations / Specializations)
– Marconi
@Alan, there is, what you can do and add foreign keys so that the two can be optional. I’ll post an example of how to create this.
– arllondias