CODEWITHSUNDEEP
mysqlnullforeign-keys
user2713461
user2713461

Reputation: 397

How to set the foreign key value as 'NULL'?

How to set foreign key in table2 value as NULL when the primary key in the table1 is deleted? I have created 2 tables Item and order_item. 

Item(item_no(primary key),qty);  
order_item(item_no(foreign key ),order_no);

I have created table order_item : 

 create table order_item(  
 item_no int references item on  delete set NULL,  
  order_no  int);

Then i have inserted 5 values in the Item table. Now ,if i delete the item_no = 4in the item table,its getting deleted in the Item table , but the value of item_nois not set to NULL in the order_item table.

Upvotes: 0

Views: 114

Answers (1)

krokodilko
krokodilko

Reputation: 36097

Please use the below syntax using explicit CONSTRAINT [name] FOREIGN KEY ... clause:

CREATE TABLE order_item1(
  item_no int,  
  order_no  int,
  constraint foreign key (item_no) references item( item_no ) on  delete set NULL 
);

or add explicitely the foreigng key to the existing table:

ALTER TABLE order_item1
ADD constraint foreign key (item_no) references item( item_no ) on  delete set NULL ;

Please take a look at this simple test case: http://www.sqlfiddle.com/#!2/3dddf/1
An inline references clause does not work.

The reason of this strange behavoiur is described in documentation, please check this link:
http://dev.mysql.com/doc/refman/5.7/en/create-table.html

MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.


Inline (inline=next to column definition) reference specyfications are parsed by MySql, but MySql simply ignores them.

Upvotes: 1

Related Questions