RE: Possible bug in referential integrity system

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.