CODEWITHSUNDEEP
mysqlforeign-keysmysql-workbench
TheCodingKing
TheCodingKing

Reputation: 97

on delete set null on update cascade MySQL

I have following problem:

This is my code in MySQL:

create table fremd(

id int primary key,
name varchar(20),
foreign key(id) references mensch(id) on delete set null

);

id is the primary key of this table but also a foreign key which is referencing to the primary key of table mensch. The problem is the on delete set null statement. When I use cascade instead of set null it works. But with set null it doesnt work. What can i do?

This is the error message:

08:39:00 Error Code: 1215. Cannot add foreign key constraint 2.609 sec

Upvotes: 0

Views: 9103

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

You have two conflicting declarations in your CREATE TABLE:

id int primary key

This basically means that id is Primary Key. Now, a Primary key basically fulfills following two constraints:

  • It will be Unique. So, no two rows cannot have same value for id.
  • It will be NOT NULL. So, it can never be set as NULL.

On the other hand, your Foreign key definition states:

foreign key(id) references mensch(id) on delete set null

ON DELETE SET NULL basically means that when the parent table id value is deleted, the child table (this table's) id value is set to NULL. This directly conflicts with the Primary Key declaration, which stops it from becoming NULL.

That is why your foreign key cannot be defined. You can now use any of the following options to resolve this. But these options will have to be carefully determined based on your business logic:

  • Make id UNIQUE key instead of Primary Key. This will ensure that it remains a Unique value, as well as NULL can be used in it.
  • Change Foreign Key's ON DELETE behvaiour to something else, like, CASCADE (this will delete the child table row, when parent table row is deleted), or, RESTRICT (this will block the deletion from parent table).

Upvotes: 3

Related Questions