Thread: BUG #6054: Insert to table, which has fkey to table,which is parenttable for another table - error

The following bug has been logged online:

Bug reference:      6054
Logged by:          Alex
Email address:      alexander.ochkalyuk@gmail.com
PostgreSQL version: 8.4.8
Operating system:   CentOS
Description:        Insert to table, which has fkey to table,which is
parenttable for another table - error
Details:

CREATE TABLE t1
(t1_id numeric(10,0) NOT NULL PRIMARY KEY);

CREATE TABLE t2
(t2_id numeric(10,0) NOT NULL PRIMARY KEY) INHERITS (t1);


CREATE TABLE t3
(t3_id numeric(10,0) NOT NULL PRIMARY KEY ,
t1_id numeric(10,0) NOT NULL REFERENCES t1(t1_id)) ;



INSERT INTO t2 VALUES(1,2);
INSERT INTO t3 VALUES(3,1);


ERROR:  insert or update on table "t3" violates ---
foreign key constraint "t3_t1_id_fkey"
     DETAIL:  Key (t1_id)=(1) is not present in table "t1".


BUT!!!!
SELECT t1_id FROM t1 WHERE t1_id = 1;
-->1

On Tue, Jun 7, 2011 at 8:44 AM, Alex <alexander.ochkalyuk@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06054
> Logged by: =A0 =A0 =A0 =A0 =A0Alex
> Email address: =A0 =A0 =A0alexander.ochkalyuk@gmail.com
> PostgreSQL version: 8.4.8
> Operating system: =A0 CentOS
> Description: =A0 =A0 =A0 =A0Insert to table, which has fkey to table,whic=
h is
> parenttable for another table - error
> Details:
>
> CREATE TABLE t1
> (t1_id numeric(10,0) NOT NULL PRIMARY KEY);
>
> CREATE TABLE t2
> (t2_id numeric(10,0) NOT NULL PRIMARY KEY) INHERITS (t1);
>
>
> CREATE TABLE t3
> (t3_id numeric(10,0) NOT NULL PRIMARY KEY ,
> t1_id numeric(10,0) NOT NULL REFERENCES t1(t1_id)) ;
>
>
>
> INSERT INTO t2 VALUES(1,2);
> INSERT INTO t3 VALUES(3,1);
>
>
> ERROR: =A0insert or update on table "t3" violates ---
> foreign key constraint "t3_t1_id_fkey"
> =A0 =A0 DETAIL: =A0Key (t1_id)=3D(1) is not present in table "t1".
>
>
> BUT!!!!
> SELECT t1_id FROM t1 WHERE t1_id =3D 1;
> -->1

When you use a foreign key with inheritance, only the rows that are
actually in the parent table itself are considered for purposes of the
foreign key.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company