[DERBY-3078] Better error messages needed when foreign key constraint creation fails because of delete-connection violations

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.2.2.0
• Component/s: SQL
• Labels: derby_triage10_10
• Environment: N/A
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Nick Williamson
• Created: 17/Sep/07 10:54
• Updated: 02/Oct/12 01:42

Description

Derby produces messages like this when creating a schema:

"ERROR 42915: Foreign Key 'PIN_FK1' is invalid because 'the delete rule of foreign key can not be CASCADE. (The relationship would cause another table to be delete-connected to the same table through multiple paths with different delete rules or with delete rule equal to SET NULL.)"

"ERROR 42915: Foreign Key 'VC_FK3' is invalid because 'the delete rule of foreign key must be CASCADE. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).)

In a large schema with many FK constraints, it is extremely difficult to identify the table that is actually causing the problem. Obviously, one of them will be either the table to which the constraint is being added or the table that is referenced by the constraint, but which is the other table? I have to examine all the children & parents of the tables involved in the constraint and keep working iteratively up & down through their own parents and children until I finally find two conflicting delete paths for the same table.

There have been several instances where I'm just unable to get to the bottom of the problem because of the complexity of the table relationships in my schema. The error messages need to explicitly name the tables instead of referring to "the table" and "the other table", and they need to give the name of the already-existing FK constraint that has prevented "this" FK constraint from being created successfully.