RE: Possible bug in referential integrity system

[copied to list]

My understanding, which isn't based on my experience more than reading any
standards, is that a referential foriegn key field in a table can be either,
a value from the referenced table, or null.

Null kinda implies "n/a".

So order-lines on an order might reference a stock item from the stock
table.
But you might enter an order-line where the stock item was null, meaning not
from the stock table.
_Not_ meaning that there is a stock item with code "null".

Remember that in general:
 null != null

In joins, these rows don't normally appear, so it's worth watching. I'm not
a posgresql expert (or an oracle one!) but in oracle you can use a syntax "
select a, b from t1, t2 where t1.id = t2.id (+) " which also includes rows
where the foreign key is null (setting all columns from the other table to
null in those rows).


> -----Original Message-----
> From:    Richard Ellis [SMTP:rellis@erols.com]
> Sent:    Wednesday, September 06, 2000 1:00 PM
> To:    piers.scannell@globecastne.com
> Subject:    Re: [BUGS] Possible bug in referential integrity system
>
> >     create table t1 (num int4, name text);
> >     create table t2 (ref int4 references t1 (num), val text);
> >     insert into t1 values (1, 'Widget1');
> >     insert into t2 values ( (select num from t1 where name = 'Widget1'),
> > 'Valuable');
> >     insert into t2 values ( (select num from t1 where name = 'widget2'),
> > 'Bug?');
> >
> > In the second case, the ref column in t2 isn't "not null" so it can
> > be a null. Foreign keys can be null, that is allowed, unless you
> > specify "not null". So the second insert's select gives null and
> > that's inserted into t2 correctly.
>
> Ok, then, is this my minunderstanding of how SQL works.  Here's what
> I thought things meant.
>
> t2 has a foreign key reference to t1.  Therefore, to insert a value
> into t2, a corresponding value must be present in t1.
>
> t1 has only one row, and that row has a value in the referenced
> foreign key constraint.
>
> Insertion of a null into the foreign key constraint column of t2
> should check t1 to see if at least one row has a null value.
>
> In this case, no row of t1 contains null.
>
> Therefore, the insert should fail, because there is no corresponding
> value in t1?
>
> Or am I unaware of a subtle side effect of 'null" when used in a
> foreign key constraint, in that 'null' in actuality means "do not
> check the foreign key constraint, just insert the remaining values"?
> If this is the meaning of 'null', is it documented anywhere?
>
> Thanks
>
>