MySQL error "Can not add foreign key constraint"

0

I'm starting in the SQL language. I'm having the following error:

Table 'linces.controlos' doesn't exist

I have not found the solution yet, can anyone help?

Drop Database if Exists Linces;
Create Database Linces;
Use Linces;

Create Table Linces(
Id_lince int NOT NULL,
Nome_lince char(50),
Genero char(50),
Data_obito datetime,
Id_pai int NOT NULL, 
Id_mae int NOT NULL,
Primary Key (Id_lince) 
);

Create Table Localizacoes(
Id_lince int NOT NULL,
Datahora datetime NOT NULL,
latitude double,
longitude double,
Constraint pk_Localizacoes Primary Key (Id_lince, Datahora),
Constraint ch_estr_Id_lince
Foreign Key (Id_lince)
References Linces (Id_lince) 
On Update Cascade
On Delete Cascade
);

Create Table Tecnicos(
Id_func int NOT NULL,
Nome_func char(50) Not Null,
Primary Key (Id_func)
);

Create Table Controlos(
Id_lince int NOT NULL,
Dat datetime NOT NULL,
Id_func int Not Null,
Peso double,
Estado_saude char(50) Not Null,
Constraint pk_Controlos Primary Key (Id_lince, dat, Id_func),
Constraint ch_estr_Id_lince
Foreign Key (Id_lince)
References Linces (Id_lince)
On Update Cascade
On Delete Set Null,
Constraint ch_estr_Id_func
Foreign Key (Id_func)
References Tecnicos (Id_func)
On Update Cascade
On Delete Cascade
);
    
asked by anonymous 31.05.2016 / 01:32

1 answer

1

The problem is:

Constraint ch_estr_Id_lince Foreign Key (Id_lince) References Linces (Id_lince) On Update Cascade On Delete Set Null

On this line you want to set a NULL column when it has the NOT NULL property

Id_lince int NOT NULL,

The solution is to change On Delete , even after you change it, you are creating two foreign keys of the same name:

Create Table Localizacoes(
Constraint ch_estr_Id_lince Foreign Key (Id_lince) References Linces (Id_lince) On Update Cascade On Delete Cascade
...
Create Table Controlos(
Constraint ch_estr_Id_lince Foreign Key (Id_lince) References Linces (Id_lince) On Update Cascade On Delete Set Null

The solution is to change the name of a key.

To check the specific errors, you can run query SHOW ENGINE INNODB STATUS and search for LATEST FOREIGN KEY ERROR .

    
31.05.2016 / 01:56