Thread: REFERENCES fails on derived classes
the following works:
create table t1 (id serial primary key);
create table t2 (id serial primary key);
create table t12 (
t1_id int references t1(id) on delete cascade,
t2_id int references t2(id) on delete cascade
);
but the following does not:
create table t1d () inherits(t1);
create table t1d2 (
t1d_id int references t1d(id) on delete cascade,
t2_id int references t2(id) on delete cascade
);
i'm told:
ERROR: UNIQUE constraint matching given keys for referenced table t1d not
found
i'm working in postgresql 7.1 (i686-pc-linux-gnu)
hope this is a bug (and i'm not wasting your time) and easily verifiable,
j. michael caine
On Sat, 2 Jun 2001, J. Michael Caine wrote: > the following works: > > create table t1 (id serial primary key); > create table t2 (id serial primary key); > create table t12 ( > t1_id int references t1(id) on delete cascade, > t2_id int references t2(id) on delete cascade > ); > > but the following does not: > create table t1d () inherits(t1); > create table t1d2 ( > t1d_id int references t1d(id) on delete cascade, > t2_id int references t2(id) on delete cascade > ); > > i'm told: > ERROR: UNIQUE constraint matching given keys for referenced table t1d not > found > > i'm working in postgresql 7.1 (i686-pc-linux-gnu) > > hope this is a bug (and i'm not wasting your time) and easily verifiable, Sort of. The unique constraint does not get inherited by t1d right now (nor do fk constraints inherit) so there isn't a unique constraint on t1d(id). Add a unique constraint to t1d(id) and it should work.
sszabo@megazone23.bigpanda.com writes: >Add a unique constraint to t1d(id) and it should work. ahh, great. thank you! (does this mean that the derived class' field's data may be allowed to be non-unique? interesting. thank you.
a month i was told that my possible bug submission was "sort of" a bug. the report and reply are below. now i write a follow up because i don't know how to establish a workroom. specifically, i know know that a unique constraint must be added to an inherited "id" field, even if it's a primary key in the base table. i can do this by 'CREATE INDEX', but this creates a completely new constraint. i want to share the unique 'serial' behavior between the base table and derived table, so that en entry in either table is unique between the tables. that way, when i do a 'SELECT baseTable*' kind of command, i can differentiate between two records. is there a way to do this, or could this be considered a valid bug of some sort? thank you, jmichael sszabo@megazone23.bigpanda.com writes: >On Sat, 2 Jun 2001, J. Michael Caine wrote: > >> the following works: >> >> create table t1 (id serial primary key); >> create table t2 (id serial primary key); >> create table t12 ( >> t1_id int references t1(id) on delete cascade, >> t2_id int references t2(id) on delete cascade >> ); >> >> but the following does not: >> create table t1d () inherits(t1); >> create table t1d2 ( >> t1d_id int references t1d(id) on delete cascade, >> t2_id int references t2(id) on delete cascade >> ); >> >> i'm told: >> ERROR: UNIQUE constraint matching given keys for referenced table t1d >not >> found >> >> i'm working in postgresql 7.1 (i686-pc-linux-gnu) >> >> hope this is a bug (and i'm not wasting your time) and easily >verifiable, > >Sort of. The unique constraint does not get inherited by t1d right now >(nor do fk constraints inherit) so there isn't a unique constraint on >t1d(id). Add a unique constraint to t1d(id) and it should work. >
On Mon, 11 Jun 2001, Michael Caine wrote: > a month i was told that my possible bug submission was "sort of" a bug. > the report and reply are below. > > now i write a follow up because i don't know how to establish a workroom. > specifically, i know know that a unique constraint must be added to an > inherited "id" field, even if it's a primary key in the base table. i can > do this by 'CREATE INDEX', but this creates a completely new constraint. > i want to share the unique 'serial' behavior between the base table and > derived table, so that en entry in either table is unique between the > tables. that way, when i do a 'SELECT baseTable*' kind of command, i can > differentiate between two records. is there a way to do this, or could > this be considered a valid bug of some sort? Unfortunately there's no way within the current constraints to do that kind of unique constraint, although you should get actually unique numbers out of the serial -- and those will span the two tables since it uses the same sequence, you can't guarantee that explicitly placed values will be unique. This is probably a bug, but inheritance needs alot of work in general. You might be able fake it with a insert/update trigger in plpgsql that makes sure that there are no matching rows. It wouldn't exactly be the same thing as a unique constraint, but it'd probably be close enough for most use.
great, thank you much. i wanted to know, in part, that i hadn't missed something. i'm going to use the same serial and hope that values aren't entered in the Id field (i won't provide a way to do it in my client app, so it should be safe enough). but the extra security is valuable to me, to cover all my bases. so i'd appreciate it being confirmed as "covered" by one bug report or enhancement request or another. then, when it becomes available, i'll put the extra securities in. perhaps someday i'll be able to help work on it, or some other part of postgress! right now i'm working on another freeware project so i don't have time, but..... (and if this is still an open request when i do, i'll gladly take it on!) thanks again, you've been quite helpful, jmichael sszabo@megazone23.bigpanda.com writes: >Unfortunately there's no way within the current constraints to do that >kind of unique constraint, although you should get actually unique numbers >out of the serial -- and those will span the two tables since it uses the >same sequence, you can't guarantee that explicitly placed values will be >unique. This is probably a bug, but inheritance needs alot of work in >general. > >You might be able fake it with a insert/update trigger in plpgsql >that makes sure that there are no matching rows. It wouldn't exactly >be the same thing as a unique constraint, but it'd probably be close >enough for most use. >