From: | Piers Scannell <piers(dot)scannell(at)globecastne(dot)com> |
---|---|
To: | pgsql-BUGS <pgsql-bugs(at)postgresql(dot)org> |
Subject: | RE: Possible bug in referential integrity system |
Date: | 2000-09-06 09:19:07 |
Message-ID: | F0DBB65C297FD211B06300A0C9DAFEE3290994@bert.internal.zone |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2000-09-06 13:17:03 | Error with union in sub-selects |
Previous Message | Karel Zak | 2000-09-06 07:21:09 | Re: bug in date_part() function in 6.5.2, 7.0.2 |