pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Creating foreign key constraint to child table?
5+ messages / 3 participants
[nested] [flat]

* Creating foreign key constraint to child table?
@ 2001-08-05 12:49 Allan Engelhardt <allane@cybaea.com>
  2001-08-05 13:03 ` Re: Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
  2001-08-05 19:20 ` Re: Creating foreign key constraint to child table? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-08-05 21:28 ` Re: Creating foreign key constraint to child table? "Dmitry G. Mastrukov" Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org>
  0 siblings, 3 replies; 5+ messages in thread

From: Allan Engelhardt @ 2001-08-05 12:49 UTC (permalink / raw)
  To: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Creating foreign key constraint to child table?
  2001-08-05 12:49 Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
@ 2001-08-05 13:03 ` Allan Engelhardt <allane@cybaea.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Allan Engelhardt @ 2001-08-05 13:03 UTC (permalink / raw)
  To: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Creating foreign key constraint to child table?
  2001-08-05 12:49 Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
@ 2001-08-05 19:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-08-05 19:55   ` Re: Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
  2 siblings, 1 reply; 5+ messages in thread

From: Stephan Szabo @ 2001-08-05 19:20 UTC (permalink / raw)
  To: Allan Engelhardt <allane@cybaea.com>; +Cc: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Creating foreign key constraint to child table?
  2001-08-05 12:49 Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
  2001-08-05 19:20 ` Re: Creating foreign key constraint to child table? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-08-05 19:55   ` Allan Engelhardt <allane@cybaea.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Allan Engelhardt @ 2001-08-05 19:55 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>

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 the SELECT 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=#





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Creating foreign key constraint to child table?
  2001-08-05 12:49 Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
@ 2001-08-05 21:28 ` "Dmitry G. Mastrukov" Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org>
  2 siblings, 0 replies; 5+ messages in thread

From: "Dmitry G. Mastrukov" Дмитрий Геннадьевич Мастрюков @ 2001-08-05 21:28 UTC (permalink / raw)
  To: Allan Engelhardt <allane@cybaea.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-08-05 21:28 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-05 12:49 Creating foreign key constraint to child table? Allan Engelhardt <allane@cybaea.com>
2001-08-05 13:03 ` Allan Engelhardt <allane@cybaea.com>
2001-08-05 19:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-05 19:55   ` Allan Engelhardt <allane@cybaea.com>
2001-08-05 21:28 ` "Dmitry G. Mastrukov" Дмитрий Геннадьевич Мастрюков <dmitry@taurussoft.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox