Mysql error "Cannot add Foreign key Constraint"

Asked

Viewed 857 times

0

I am starting in SQL language. I have the following error:

Table 'linces.controlos' doesn't exist

I haven’t found the solution yet, someone can 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
);
  • 1

    At what point did the error occur?

1 answer

1

The problem is in:

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 is with the property NOT NULL

Id_lince int NOT NULL,

The solution is to change the On Delete, even after changing it, you are creating two foreign keys with 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 rename a key.

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

Browser other questions tagged

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