MySQL Bugs: #10468: Foreign Key: ON DELETE NO ACTION option does not work for foreign key.

Bug #10468	Foreign Key: ON DELETE NO ACTION option does not work for foreign key.
Submitted:	9 May 2005 11:05	Modified:	9 May 2005 11:25
Reporter:	Disha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.4 Beta	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
The value from a referenced column cannot be deleted though the foreign key is set with ON DELETE NO ACTION option.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to // and use the test database.
3. Create a table as follows:
   create table t1(f1 int, f2 char(100), primary key(f1)) engine=innodb//

4. Create another table with foreign key to column f1 of table t1 with the ON DELETE NO ACTION option:
   create table t2(f1 int, f2 char(100), foreign key (f1) references t1(f1) on delete no action) engine=innodb//
5. Now insert values in both tables:
   insert into t1 values(10,'test1')//
   insert into t2 values(10,'test2')//

6. Now try to delete the value from table t1
   delete from t1 where f1=10//

Expected Results: 
1. Deleting of value from parent should be possible without error.

Actual Results: 
1. Error is displayed as:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails.

Thank you for your bug report. However, this is not a bug.

Under the ANSI SQL-92 standard, NO ACTION means "no action" in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table (Gruber, 2000:181). It is therefore the ANSI syntax for explicitly enforcing referential integrity and is supported by SQL Server 2000, Oracle 9 amongst other database systems. 

As of 4.0.18 InnoDB checks the foreign key constraint, not ignore it. Since we do not have
deferred constraints in InnoDB, this makes InnoDB to check NO ACTION constraints immediately, like it checks RESTRICT constraints.

Regards,
    JanL

Hi,

I will add some additional detailed documentation of the referential action specified using ON UPDATE and ON DETETE subclauses of the FOREIGN KEY clause.

Regards
    JanL