Обсуждение: Possible bug in referential integrity system
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.
Hello! Keys phrase 'NOT NULL' simple=# create table t1 (num int4 PRIMARY KEY, name text); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 't1_pkey' for table 't1' CREATE simple=# create table t2 (ref int4 references t1 (num) NOT NULL, val text); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE simple=# insert into t1 values (1, 'Widget1'); INSERT 80324 1 simple=# insert into t2 values ( (select num from t1 where name = 'widget2'), 'Bug?'); ERROR: ExecAppend: Fail to add null value in not null attribute ref > => 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 Alexei E. Korneyev alexei@niva.sposad.ru
I'm not sure what the supposed bug is...
> From: Alexei E Korneyev [mailto:alexei@niva.sposad.ru]
[ reformated by PS]
create table t1 (num int4 PRIMARY KEY, name text);
create table t2 (ref int4 references t1 (num) NOT NULL, val text);
insert into t1 values (1, 'Widget1');
insert into t2 values ( (select num from t1 where name = 'widget2'),
'Bug?');
-- ERROR: ExecAppend: Fail to add null value in not null attribute
ref
In the first cast, that's what you'd expect, isn't it? widget2 doesn't
appear in t1 so the select doesn't return a value, you have to have one for
the not null column in t2 and so it can't work.
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.
[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 > >
Actually, this is what the spec defines. You're using match unspecified, which means: - If no <match type> was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table. Stephan Szabo sszabo@bigpanda.com On Sat, 2 Sep 2000, Richard Ellis wrote: > 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.