[firebird-support] Compound foreign key & null value bug?
I'm modifying a foreign key from one column to two. I dropped the original foreign key from the child table, populated the new columns, and then created the new key. I forgot to populate the new column in the child table, so all the relationships looked like this: ChildParent --- Col1:null -> Col1:A Col2:B-> Col2:B The new foreign keys were created without any errors. Since (null,B) doesn't match anything, shouldn't the creation have failed?
Re: [firebird-support] Compound foreign key & null value bug?
Rick, > I forgot to populate the new column in the child table, so all the > relationships looked like this: > > ChildParent > --- > Col1:null -> Col1:A > Col2:B-> Col2:B > > The new foreign keys were created without any errors. Since (null,B) > doesn't match anything, shouldn't the creation have failed? > Since you want a quick answer, I believe that behavior follows the standard. I have it in front of me, but digging out the expected null behavior is a pain, but from the fact that one of the acceptable behaviors of a cascading update or delete is to set the matching field(s) in the child table to null, I'd have to say that for foreign key relationships a null in the referencing field is considered to match. Good luck, Ann [Non-text portions of this message have been removed]
RE: [firebird-support] Compound foreign key & null value bug?
> I'd have to say that for foreign key relationships a null in the referencing > field is considered to match How about in the referenced field? BTW, thanks for the quick reply. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison Sent: Thursday, October 25, 2012 10:33 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Compound foreign key & null value bug? Rick, > I forgot to populate the new column in the child table, so all the > relationships looked like this: > > Child Parent > - -- > Col1:null -> Col1:A > Col2:B -> Col2:B > > The new foreign keys were created without any errors. Since (null,B) > doesn't match anything, shouldn't the creation have failed? > Since you want a quick answer, I believe that behavior follows the standard. I have it in front of me, but digging out the expected null behavior is a pain, but from the fact that one of the acceptable behaviors of a cascading update or delete is to set the matching field(s) in the child table to null, I'd have to say that for foreign key relationships a null in the referencing field is considered to match. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Compound foreign key & null value bug?
On Thu, Oct 25, 2012 at 12:49 PM, Rick Debay wrote: > > I'd have to say that for foreign key relationships a null in the > referencing field is considered to match > > How about in the referenced field? BTW, thanks for the quick reply. > The referenced fields have to be subject to a primary or unique key constraint. Primary is easy. No nulls. Unique constraints are not so very clear and various databases handle them differently. My understanding of the standard is that if you have a Unique constraint the following pairs of values are OK: (A, B), (A, null), (null, B) but that you could not have two pairs (A, null) and (A, null). Cheers, Ann [Non-text portions of this message have been removed]
RE: [firebird-support] Compound foreign key & null value bug?
Anyone available for a quick reply? Schools are closing early for hurricane Sandy and I'd like to resolve this before an involuntary long weekend babysitting :-( -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay Sent: Wednesday, October 24, 2012 10:50 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Compound foreign key & null value bug? I'm modifying a foreign key from one column to two. I dropped the original foreign key from the child table, populated the new columns, and then created the new key. I forgot to populate the new column in the child table, so all the relationships looked like this: ChildParent --- Col1:null -> Col1:A Col2:B-> Col2:B The new foreign keys were created without any errors. Since (null,B) doesn't match anything, shouldn't the creation have failed? ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
