Thread: Creating foreign key constraint to child table?

I would like to create a FOREIGN KEY constraint to an inherited column, like:
   test=# CREATE TABLE foo(id INTEGER PRIMARY KEY);   test=# CREATE TABLE bar() INHERITS (foo);   test=# CREATE TABLE
baz(bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES bar(id));   ERROR:  UNIQUE constraint matching given
keysfor referenced table "bar" not found
 

This obvioulsy doesn't work.  I *can* create a FOREIGN KEY contraint to the parent table:
   test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id));   NOTICE:  CREATE
TABLEwill create implicit trigger(s) for FOREIGN KEY check(s)   CREATE
 

but this is not exactly what I want: I need to ensure that baz.bar is a bar and not just any foo.

Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is there a nice way to do this?

Any examples on how to do this?  In particular, do I need to do a SELECT on pg_class for every INSERT / UPDATE in baz,
justto get the tableoid for bar ?  There *is* an index on pg_class.relname but still...
 

   --- Allan.

I obviously haven't had enough coffee yet... :-)  The following script works as expected.

drop database test;
create database test;
\c test
create table foo (id integer primary key);
create table bar () inherits (foo);
create unique index bar_id_idx ON bar(id);
create table baz (bar integer,      constraint fk_bar foreign key (bar) references bar(id));
insert into foo values (1);
insert into bar values (2);
insert into baz values (2);
insert into baz values (1); -- fails

Sorry.

   --- Allan.

I wrote:

> I would like to create a FOREIGN KEY constraint to an inherited column, like:
>
>     test=# CREATE TABLE foo(id INTEGER PRIMARY KEY);
>     test=# CREATE TABLE bar() INHERITS (foo);
>     test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES bar(id));
>     ERROR:  UNIQUE constraint matching given keys for referenced table "bar" not found
>
> This obvioulsy doesn't work.  I *can* create a FOREIGN KEY contraint to the parent table:
>
>     test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id));
>     NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
>     CREATE
>
> but this is not exactly what I want: I need to ensure that baz.bar is a bar and not just any foo.
>
> Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is there a nice way to do this?
>
> Any examples on how to do this?  In particular, do I need to do a SELECT on pg_class for every INSERT / UPDATE in
baz,just to get the tableoid for bar ?  There *is* an index on pg_class.relname but still...
 
>
>     --- Allan.

On Sun, 5 Aug 2001, Allan Engelhardt wrote:

>     test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id));
>     NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
>     CREATE
> 
> but this is not exactly what I want: I need to ensure that baz.bar is
> a bar and not just any foo.
> 

Not that this is related to what you asked about precisely (I saw the
response you made), but the query above also doesn't do what you think
it does right now.  It currently makes a reference to only foo, not
any subchildren of foo.

Stephan Szabo wrote:

> On Sun, 5 Aug 2001, Allan Engelhardt wrote:

[see new example below]

> Not that this is related to what you asked about precisely (I saw the
> response you made), but the query above also doesn't do what you think
> it does right now.  It currently makes a reference to only foo, not
> any subchildren of foo.

Oh, man!  You are right, but this sux big time: there should not be an asymmetry between a FOREIGN KEY constraint and
theSELECT statement.  Now that the default is SQL_INHERITANCE ON, it should be the default for the constraint as well,
IMHO.

1. Am I the only one who thinks this is a bug?

2. How would I get the behaviour I expect?  Write my own trigger? :-P

   --- Allan.


test=# create table foo (id integer primary key);
test=# create table bar () inherits (foo);
test=# create table baz (bar integer,      constraint fk_bar foreign key (bar) references foo(id));
test=# insert into foo values (1);
test=# insert into bar values (2);
test=# insert into baz values (2);
ERROR:  fk_bar referential integrity violation - key referenced from baz not found in foo
test=# select * from foo where id = 2;id
---- 2
(1 row)

test=#

05 Aug 2001 13:49:22 +0100, Allan Engelhardt ____
> I would like to create a FOREIGN KEY constraint to an inherited column, like:
> 
>     test=# CREATE TABLE foo(id INTEGER PRIMARY KEY);
>     test=# CREATE TABLE bar() INHERITS (foo);
>     test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES bar(id));
>     ERROR:  UNIQUE constraint matching given keys for referenced table "bar" not found
> 
> This obvioulsy doesn't work.  I *can* create a FOREIGN KEY contraint to the parent table:
> 
>     test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id));
>     NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
>     CREATE
> 
> but this is not exactly what I want: I need to ensure that baz.bar is a bar and not just any foo.
> 
> Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is there a nice way to do this?
> 
> Any examples on how to do this?  In particular, do I need to do a SELECT on pg_class for every INSERT / UPDATE in
baz,just to get the tableoid for bar ?  There *is* an index on pg_class.relname but still...
 
> 
Now child table does not inherit constraints from parent. More of that,
you can violate primary key in parent by inserting duplicate id in
child. So inheritance is broken in current version. You should develop
scheme without inheritance.

Regards,
Dmitry

Dimitri pointed out (the post does not seem to have appered yet) that you can also do:

    test=# create table foo(id integer primary key);    NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'foo_pkey'for table 'foo'    CREATE    test=# create table bar () inherits (foo);    CREATE    test=# insert into foo
values(1);    INSERT 12734236 1    test=# insert into foo values (1);    ERROR:  Cannot insert a duplicate key into
uniqueindex foo_pkey    test=# insert into bar values (1);    INSERT 12734238 1    test=# select * from foo;     id
----     1      1    (2 rows)
 

So inheritance does seem to be completely broken.  There is also an entry in the TODO list

    Allow inherited tables to inherit index, UNIQUE constraint, and primary key, foreign key [inheritance]

which seems to be related.  It doesn't have a dash, so I guess I won't hold my breath....


Now I'm sad.


Allan.


Allan Engelhardt wrote:

> Stephan Szabo wrote:
>
> > On Sun, 5 Aug 2001, Allan Engelhardt wrote:
>
> [see new example below]
>
> > Not that this is related to what you asked about precisely (I saw the
> > response you made), but the query above also doesn't do what you think
> > it does right now.  It currently makes a reference to only foo, not
> > any subchildren of foo.
>
> Oh, man!  You are right, but this sux big time: there should not be an asymmetry between a FOREIGN KEY constraint and
theSELECT statement.  Now that the default is SQL_INHERITANCE ON, it should be the default for the constraint as well,
IMHO.
>
> 1. Am I the only one who thinks this is a bug?
>
> 2. How would I get the behaviour I expect?  Write my own trigger? :-P
>
>     --- Allan.
>
> test=# create table foo (id integer primary key);
> test=# create table bar () inherits (foo);
> test=# create table baz (bar integer,
>        constraint fk_bar foreign key (bar) references foo(id));
> test=# insert into foo values (1);
> test=# insert into bar values (2);
> test=# insert into baz values (2);
> ERROR:  fk_bar referential integrity violation - key referenced from baz not found in foo
> test=# select * from foo where id = 2;
>  id
> ----
>   2
> (1 row)
>
> test=#