Thread

1. Possible bug in referential integrity system

   Richard Ellis <rellis@erols.com> — 2000-09-02T06:04:33Z

   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.
   
   

2. Re: Possible bug in referential integrity system

   Alexei E Korneyev <alexei@niva.sposad.ru> — 2000-09-06T05:42:59Z

   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
   
   
   
   

3. Re: Possible bug in referential integrity system

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-09-06T16:40:56Z

   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.