Skip site navigation (1) Skip section navigation (2)

RE: Possible bug in referential integrity system

From: Piers Scannell <piers(dot)scannell(at)globecastne(dot)com>
To: "'rellis(at)erols(dot)com'" <rellis(at)erols(dot)com>
Cc: "'pgsql-bugs(at)postgresql(dot)org'" <pgsql-bugs(at)postgresql(dot)org>
Subject: RE: Possible bug in referential integrity system
Date: 2000-09-06 15:21:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
[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
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 = (+) " 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(at)erols(dot)com]
> Sent:	Wednesday, September 06, 2000 1:00 PM
> To:	piers(dot)scannell(at)globecastne(dot)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

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2000-09-06 16:35:42
Subject: Re: bugs
Previous:From: Tom LaneDate: 2000-09-06 14:47:56
Subject: Re: /usr/include/pgsql/os.h does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group