Erro Foreign Key

Asked

Viewed 401 times

2

I’m making a database rather simple for presentation of a work and I’m finding the same annoying problem.

In this case we have two tables, funcionario[nome,cpf] and departamento[DNR,CPF do gerente]. Here is the code:

CREATE SCHEMA empresa;
USE empresa;

CREATE TABLE funcionario (
   nome VARCHAR(30),
   CPF INT NOT NULL,
   DNR INT NOT NULL,
   PRIMARY KEY (CPF),
   FOREIGN KEY (DNR) REFERENCES DEPARTAMENTO(DNR)
);

CREATE TABLE DEPARTAMENTO
(
   CPF INT NOT NULL,
   DNR INT NOT NULL,
   PRIMARY KEY (DNR),
   FOREIGN KEY (CPF) REFERENCES funcionario(CPF)
);

I don’t understand why I can’t do this. Thank you in advance.

mysql sql database

  • Welcome to Stackoverflow Pedro. I made some changes to improve the view, any problem you can reverse edits through of this link.

  • I just don’t understand why a department has a key in Employee. This creates a 1-1 ratio, meaning a department can only have one employee. Maybe the key just needs to be on the job. Creating an Employee Relationship belongs to a department and a department owns N employees.

2 answers

2

This error happens because the table that will be referenced in Foreign Key must exist at the time of key creation.

In this case you must enter the keys in a separate query:

CREATE TABLE funcionario (
   nome VARCHAR(30),
   CPF INT NOT NULL,
   DNR INT NOT NULL,
   PRIMARY KEY (CPF)
);

CREATE TABLE DEPARTAMENTO
(
   CPF INT NOT NULL,
   DNR INT NOT NULL,
   PRIMARY KEY (DNR)
);

ALTER TABLE funcionario
ADD FOREIGN KEY (DNR) REFERENCES DEPARTAMENTO(DNR);

ALTER TABLE DEPARTAMENTO
ADD FOREIGN KEY (CPF) REFERENCES funcionario(CPF);

Example in SQL Fiddle.

-1

You won’t be able to do that because your table funcionario points to the table departamento and vice versa.

You must create a new table to bridge the tables funcionario and departamento.

Example:


CREATE TABLE funcionario (
   CPF INT PRIMARY KEY,
   NOME VARCHAR(30)
);

CREATE TABLE DEPARTAMENTO (
   DNR INT PRIMARY KEY,
   CPF INT NOT NULL
);

CREATE TABLE funcionario_departamento (
   CPF_FUNCIONARIO INT NOT NULL,
   DNR_DEPARTAMENTO INT NOT NULL,
   FOREIGN KEY (CPF_FUNCIONARIO) REFERENCES funcionario(CPF),
   FOREIGN KEY (DNR_DEPARTAMENTO) REFERENCES departamento(DNR)
);

Hug!

  • If the relation is not n-n it does not need to create a third table. Just create FK after both are created.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.