MYSQL - FK ERROR

Navigation
1

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

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 do not know what to do, I'm having to pack and nothing, could you 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)
);
    
asked by anonymous 13.09.2015 / 01:03

1 answer

2

I'm not using MySql Server, but giving an adapted one in your SQL I discovered two things:

  • You are trying to create the foreign keys before the tables, this is impossible.
  • The foreign key field codBanco of the employee table reference field codigo of the bank table, which in addition to allowing null is not a primary key (should reference the table id).

    • Fixed these bugs and it worked fine here.

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

    In this way, I recommend that you create the scripts separately, one of which is:

  • Create the database and select it - i.e% with%.

  • Create tables ONLY - 1-CriaESelecionaBanco.sql .

  • Then create the primary keys of the tables - 2-CriaTabelas.sql .
  • Lastly ONLY foreign keys - 3-CriaChavesPrimarias.sql .

    • Doing this becomes a lot harder to make a mistake.

        
    13.09.2015 / 02:10
    How to maintain ordered list of records in relationship N: N php regex to get 2 href link groups