Skip site navigation (1) Skip section navigation (2)

RE: Possible bug in referential integrity system

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 (view raw or flat)
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.
 

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-09-06 13:17:03
Subject: Error with union in sub-selects
Previous:From: Karel ZakDate: 2000-09-06 07:21:09
Subject: Re: bug in date_part() function in 6.5.2, 7.0.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group