Better error messages needed when foreign key constraint creation fails because
of delete-connection violations
---------------------------------------------------------------------------------------------------------------
Key: DERBY-3078
URL: https://issues.apache.org/jira/browse/DERBY-3078
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.2.2.0
Environment: N/A
Reporter: Nick Williamson
Priority: Minor
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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
