Unraveling FK errors

["Williamson, Nick" <ni...@mentor.com>]

Hi all,
 
If you get errors like these when you're 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).) '."

...which table is it referring to when it says "the table" and "the same
table"? The table to which the constraint is being added, or the table
that is referenced by the constraint? It's really hard - for me at least
- to work all of this out because I have c.500 tables in the schema and
the relationships are quite complex; a table often has several FK
constraints (and each of the referenced tables can have several FK
constraints) and so it isn't immediately obvious which tables are
causing the problem. Is there a way of getting Derby to provide more
detail on exactly what the problem is? 

TIA
Nick

RE: Unraveling FK errors

["Williamson, Nick" <ni...@mentor.com>]

OK, thanks - will do.

 

-----Original Message-----
From: Dyre.Tjeldvoll@Sun.COM [mailto:Dyre.Tjeldvoll@Sun.COM] 
Sent: 17 September 2007 10:09
To: Derby Discussion
Subject: Re: Unraveling FK errors

<snip>

You could log an enhancement request in Jira for this.

Re: Unraveling FK errors

[Dy...@Sun.COM]

"Williamson, Nick" <ni...@mentor.com> writes:

> Hi all,
>  
> If you get errors like these when you're 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).) '."
>
> ...which table is it referring to when it says "the table" and "the same
> table"? The table to which the constraint is being added, or the table
> that is referenced by the constraint? It's really hard - for me at least
> - to work all of this out because I have c.500 tables in the schema and
> the relationships are quite complex; a table often has several FK
> constraints (and each of the referenced tables can have several FK
> constraints) and so it isn't immediately obvious which tables are
> causing the problem. Is there a way of getting Derby to provide more
> detail on exactly what the problem is? 

Normally the problematic query is echoed before the error message, but
I assume that didn't help in this case. I don't think there is
a way of making the error messages more verbose.

It might be possible to make improvements in the cases you
mention. It's not hard to add parameters to the error message
that would identify the tables, but I don't know if this info is
easily available at the point where the exception is thrown.

You could log an enhancement request in Jira for this.

-- 
dt