RE: Possible bug in referential integrity system

I'm not sure what the supposed bug is...

> From: Alexei E Korneyev [mailto:alexei(at)niva(dot)sposad(dot)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.