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: | 200009020604.CAA01196@i386.rle.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The submit button on the form located at
http://www.postgresql.org/bugs/bugs.php?1 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
=> create table t2 (ref int4 references t1 (num), val text);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
=> 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. http://www0.vcnet.com/bms
No is the answer.
Microsoft: Bringing you ten-year old technology, tomorrow, maybe.
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2000-09-02 09:18:18 | LIKE 'bla%' |
Previous Message | Tom Lane | 2000-09-01 21:54:27 | Re: select on macaddr field type yields incorrect response |