Thread: child table doesn't inherit PRIMARY KEY?
When creating a child (through CREATE TABLE ... INHERIT (parent)) it seems the child gets all of the parent's contraints _except_ its PRIMARY KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time I create an inherited table? Cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr If at first you don't succeed, redefine success.
On Sat, Jun 03, 2000 at 05:22:56PM +0200, Louis-David Mitterrand wrote:
> When creating a child (through CREATE TABLE ... INHERIT (parent)) it
> seems the child gets all of the parent's contraints _except_ its PRIMARY
> KEY. Is this normal? Should I add a PRIMARY KEY(id) statement each time
> I create an inherited table?
Following up to my previous message, I found that one can't explicitely
add a PRIMARY KEY on child table referencing a field on the parent
table, for instance:
CREATE TABLE auction (
id SERIAL PRIMARY KEY,
title text,
... etc...
);
then
CREATE TABLE auction_dvd (
zone int4,
PRIMARY KEY("id")
) inherits("auction");
doesn't work:
ERROR: CREATE TABLE: column 'id' named in key does not exist
But the aution_dvd table doesn't inherit the auction table's PRIMARY
KEY, so I can insert duplicates.
Solutions:
1) don't use PRIMARY KEY, use UNIQUE NOT NULL (which will be inherited?)
but the I lose the index,
2) use the OID field, but it's deprecated by PG developers?
What would be the best solution?
TIA
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr
Veni, Vidi, VISA.
Louis-David Mitterrand writes: > When creating a child (through CREATE TABLE ... INHERIT (parent)) it > seems the child gets all of the parent's contraints _except_ its PRIMARY > KEY. Is this normal? It's kind of a bug. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Sun, Jun 04, 2000 at 03:46:53AM +0200, Peter Eisentraut wrote: > Louis-David Mitterrand writes: > > > When creating a child (through CREATE TABLE ... INHERIT (parent)) it > > seems the child gets all of the parent's contraints _except_ its PRIMARY > > KEY. Is this normal? > > It's kind of a bug. Is it a well-known bug or have I discovered it? ;-) (I am sending a copy of the bug report to -hackers) Thanks, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr "God is a mathematician of very high order, and he used very advanced mathematics in constructing the universe." (Dirac)
Can someone comment on this? [ Charset ISO-8859-1 unsupported, converting... ] > Louis-David Mitterrand writes: > > > When creating a child (through CREATE TABLE ... INHERIT (parent)) it > > seems the child gets all of the parent's contraints _except_ its PRIMARY > > KEY. Is this normal? > > It's kind of a bug. > > > -- > Peter Eisentraut Sernanders v?g 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Does this bug still exist? [ Charset ISO-8859-1 unsupported, converting... ] > Louis-David Mitterrand writes: > > > When creating a child (through CREATE TABLE ... INHERIT (parent)) it > > seems the child gets all of the parent's contraints _except_ its PRIMARY > > KEY. Is this normal? > > It's kind of a bug. > > > -- > Peter Eisentraut Sernanders v?g 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Probably, since I see it in near recent sources (and it affects UNIQUE as well. As I remember it, the last discussion on this couldn't determine what the correct behavior for unique/primary key constraints was in the inheritance case (is it a single unique hierarchy through all the tables [would be needed for fk to inheritance trees] or separate unique constraints for each table [which would be similar to how many people seem to currently use postgres inheritance as a shortcut]). On Thu, 18 Jan 2001, Bruce Momjian wrote: > Does this bug still exist? > > [ Charset ISO-8859-1 unsupported, converting... ] > > Louis-David Mitterrand writes: > > > > > When creating a child (through CREATE TABLE ... INHERIT (parent)) it > > > seems the child gets all of the parent's contraints _except_ its PRIMARY > > > KEY. Is this normal?