MySQL Bugs: #15746: Foreign Keys: Self-referential constraints incorrectly prevent deletes

Bug #15746	Foreign Keys: Self-referential constraints incorrectly prevent deletes
Submitted:	14 Dec 2005 16:37	Modified:	1 Oct 2009 22:52
Reporter:	David Moore	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.15	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
 A self-referential constraint incorrectly prevents delete of row where constraint links it to same row.

 This seems to be different from bug 15046.

 A reasonable example would be for an employee, where the person responsible for checking their expenses is stored against the employee.  The constaint defining this sitiuation would link the employee table to itself.

 Because of this bug the employee row could never be deleted if the employee checks their own expenses.

 The code below produces the error with the simplest setup I could coome up with.

How to repeat:
If the following script fragment is run:

DROP TABLE TRS_COST_CENTREX;

CREATE TABLE TRS_COST_CENTREX
(
  COST_CENTRE                   VARCHAR(24)  BINARY NOT NULL,
  COST_CENTRE_01                VARCHAR(24)  BINARY NOT NULL,
  CONSTRAINT PK_TRS_COST_CENTREX PRIMARY KEY (COST_CENTRE)
)
ENGINE = InnoDB;

CREATE INDEX COST_CENTREX_INX11 ON TRS_COST_CENTREX ( COST_CENTRE_01 );
ALTER TABLE TRS_COST_CENTREX ADD CONSTRAINT COST_CENTREX_11 FOREIGN KEY (COST_CENTRE_01)
 REFERENCES TRS_COST_CENTREX (COST_CENTRE) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO TRS_COST_CENTREX VALUES( 'XXX', 'XXX');

DELETE FROM TRS_COST_CENTREX WHERE COST_CENTRE = 'XXX';

The script will fail on the delete with the message:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 (`trs/trs_cost_centrex`, CONSTRAINT `COST_CENTREX_11` FOREIGN KEY (`COST_CENTRE_01`)
 REFERENCES `trs_cost_centrex` (`COST_CENTRE`))

Thank you for a problem report. Yes, everything works just as you described:

mysql> DELETE FROM TRS_COST_CENTREX WHERE COST_CENTRE = 'XXX';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/TRS_COST_CENTREX`, CONSTRAINT `COST_CENTREX_11` FOREIGN KEY (`COST_CENTRE_01`) REFERENCES `TRS_COST_CENTREX` (`COST_CENTRE`))

But it looks like a documented feature (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html) to me:

"RESTRICT: Rejects the delete or update operation for the parent table. NO ACTION  and RESTRICT are the same as omitting the ON DELETE or ON UPDATE  clause. (Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT  are the same.)"

So, that is what you explicitely asked for. Try CASCADE, if you want to allow such operations.

Thanks for pointing out where this is covered in the Manual, as obviously I hadn't spotted this.  However, I think the more relevant section is(last sentence):

"Deviation from SQL standards:
   Like MySQL in general, in an SQL statement that inserts, deletes, or updates
 many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. 
 According to the SQL standard, the default behavior should be deferred checking,
 that is, constraints are only checked after the entire SQL statement has been
 processed. Until InnoDB implements deferred constraint checking, some things will
 be impossible, such as deleting a record that refers to itself via a foreign key."

 Your suggestion of using CASCADE would not be suitable as I am trying to prevent deletes/updates leaving orphan rows.  I will implement the constraint in business logic instead.

 Incidently, Oracle and SQL Server do allow deleting a record that refers to itself via a foreign key.

So, as your particular case is documented, can we make this report a (verified, and useful) feature request? For developers to get yet another reason to implement the standard behaviour? I believe, they are already doing it, but not for 5.0.x...

As this is already known behaviour, I'm quite happy to make this a feature request instead of a bug.  Best of luck!

This is a duplicate of Bug#7412 Cannot delete a row that references itself.