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

Possible bug in referential integrity system

From: Richard Ellis <rellis(at)erols(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Possible bug in referential integrity system
Date: 2000-09-02 06:04:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
The submit button on the form located at results in a report of a
parsing error, so I'm sending this here as the alternative.

Is the following a bug in the referential integrity system?  This is
for PG 7.0.0.  I realize that declaring the "ref" column in t2
additionally as "not null" would prevent this.  However, why does the
"references" check allow insertion of a null value into t2 when there
are no corresponding null value in the num column of t1?

If this is actually fixed in 7.0.2, then please accept my apologies.


=> create table t1 (num int4, name text);
=> create table t2 (ref int4 references t1 (num), val text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

=> insert into t1 values (1, 'Widget1');
INSERT 17518650 1

=> insert into t2 values ( (select num from t1 where name = 'Widget1'), 'Valuable');
INSERT 17518651 1
=> insert into t2 values ( (select num from t1 where name = 'widget2'), 'Bug?');
INSERT 17518652 1

=> select * from t2;
 ref |   val
   1 | Valuable
     | Bug?
(2 rows)

=> select * from t2 where ref is null;
 ref | val
     | Bug?
(1 row)

Microsoft is not the answer.            Boycott Microsoft Home page
Microsoft is the question.    
No is the answer.

Microsoft: Bringing you ten-year old technology, tomorrow, maybe.


pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-09-02 09:18:18
Subject: LIKE 'bla%'
Previous:From: Tom LaneDate: 2000-09-01 21:54:27
Subject: Re: select on macaddr field type yields incorrect response

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