Thread

RE: Possible bug in referential integrity system

Piers Scannell <piers.scannell@globecastne.com> — 2000-09-06T09:19:07Z

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.