Foreign Key Error

Question

Foreign Key Error

Navigation

#1 by (1 votes)
#2 by (-1 votes)

2

I'm making a somewhat simple database for submitting a job and I'm encountering the same annoying problem.

In the 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 do not understand why I can not do this. Thank you in advance.

mysql sql

asked by anonymous 08.05.2015 / 08:10

2 answers

Access websites and extract contents to excel via VBA Combobox.Items not being updated

score 1 · Answer 1

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

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 on SQL Fiddle .

score -1 · Answer 2

You will not be able to do this, since your funcionario table points to the departamento table and vice versa.

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

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)
);

Hugs!