MYSQL - FK ERROR

Asked

Viewed 364 times

1

Hi, could you help me? I am trying to add the FK in the employees table, only it is giving this error OBS I am creating the other tables before the employees

20:29:15    CREATE TABLE Funcionarios( id INT(5) NOT NULL AUTO_INCREMENT, nomeCompleto VARCHAR(120) NOT NULL, cpf VARCHAR(11) NOT NULL, rg VARCHAR(15) NOT NULL, pis VARCHAR(20) NOT NULL, numeroDaCarteira VARCHAR(25) NOT NULL, dataNascimento DATE NOT NULL, dataEntrada DATE NOT NULL, dataSaida DATE NULL, endereco VARCHAR(80), bairro VARCHAR(50), cidade VARCHAR(50), estado CHAR(2), telefone VARCHAR(15), salario VARCHAR(10), agencia VARCHAR(10), conta VARCHAR(10), idCargo INT(5), idSetor INT(5), codBanco INT(5), idEstadocivil INT(5),   PRIMARY KEY (id),  FOREIGN KEY (idCargo) REFERENCES Cargo(id), FOREIGN KEY (idSetor) REFERENCES Setor (id), FOREIGN KEY (codBanco) REFERENCES Banco (codigo), FOREIGN KEY (idEstadocivil) REFERENCES EstadoCivil (id)  )  Error Code: 1215. Cannot add foreign key constraint 0.735 sec

I don’t know what to do, I’m getting fixed and nothing, could help me?

CREATE DATABASE Empresa2;

CREATE TABLE Funcionarios(
id INT(5) NOT NULL AUTO_INCREMENT,
nomeCompleto VARCHAR(120) NOT NULL,
cpf VARCHAR(11) NOT NULL,
rg VARCHAR(15) NOT NULL,
pis VARCHAR(20) NOT NULL,
numeroDaCarteira VARCHAR(25) NOT NULL,
dataNascimento DATE NOT NULL,
dataEntrada DATE NOT NULL,
dataSaida DATE NULL,
endereco VARCHAR(80),
bairro VARCHAR(50),
cidade VARCHAR(50),
estado CHAR(2),
telefone VARCHAR(15),
salario VARCHAR(10),
agencia VARCHAR(10),
conta VARCHAR(10),
idCargo INT(5),
idSetor INT(5),
codBanco INT(5),
idEstadocivil INT(5),


PRIMARY KEY (id),

FOREIGN KEY (idCargo) REFERENCES Cargo(id),
FOREIGN KEY (idSetor) REFERENCES Setor (id),
FOREIGN KEY (codBanco) REFERENCES Banco (codigo),
FOREIGN KEY (idEstadocivil) REFERENCES EstadoCivil (id)

);

CREATE TABLE EstadoCivil(
id INT(5) NOT NULL AUTO_INCREMENT,
referencia VARCHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE Cargo(
id INT(5) NOT NULL AUTO_INCREMENT,
referencia VARCHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE Setor(
id INT(5) NOT NULL AUTO_INCREMENT,
referencia VARCHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE Banco(
id INT(5) NOT NULL AUTO_INCREMENT,
referencia VARCHAR(50),
codigo int(5),
PRIMARY KEY (id)
);
mysql sql mysql-workbench

  • You are creating the table Funcionarios2 after the others? And define foreign fields as UNSIGNED: idSetor INT(5) UNSIGNED

  • No, I created the others before, and then to employees

1 answer

2


I am not using Mysql Server, but giving an adapted in your SQL discovered two things:

  1. You’re trying to create the foreign keys before the tables, that is impossible.
  2. The foreign key field codBanco of the staff table reference the field codigo bank table, which in addition to allow nulls is not primary key (must reference the table id).

Fixed these errors and worked normal here.

Now, one thing I always recommend doing is separating the scripts by task, otherwise it will turn into a cob and you will never understand.

This way, I recommend that you create the scripts separately, being one that:

  1. Create the bank and select it - i.e 1-CriaESelecionaBanco.sql.

  2. Create ONLY the tables - 2-CriaTabelas.sql.

  3. Then create the table primary keys - 3-CriaChavesPrimarias.sql .
  4. Finally ONLY foreign keys - 4-CriaChavesEstrangeiras.sql.

Doing this makes it a lot harder to miss.

Browser other questions tagged mysql sql mysql-workbench

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