pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedchild table doesn't inherit PRIMARY KEY?
10+ messages / 4 participants
[nested] [flat]
* child table doesn't inherit PRIMARY KEY?
@ 2000-06-03 15:22 Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 2 replies; 10+ messages in thread
From: Louis-David Mitterrand @ 2000-06-03 15:22 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2000-06-03 15:51 Louis-David Mitterrand <cunctator@apartia.ch>
parent: Louis-David Mitterrand <cunctator@apartia.ch>
1 sibling, 0 replies; 10+ messages in thread
From: Louis-David Mitterrand @ 2000-06-03 15:51 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2000-06-04 01:46 Peter Eisentraut <peter_e@gmx.net>
parent: Louis-David Mitterrand <cunctator@apartia.ch>
1 sibling, 3 replies; 10+ messages in thread
From: Peter Eisentraut @ 2000-06-04 01:46 UTC (permalink / raw)
To: ldm@apartia.com; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2000-06-04 07:20 Louis-David Mitterrand <cunctator@apartia.ch>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 10+ messages in thread
From: Louis-David Mitterrand @ 2000-06-04 07:20 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-general@postgresql.org
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2000-10-02 16:29 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2000-10-02 16:29 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: ldm@apartia.com; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2001-01-19 04:57 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-01-19 04:57 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: ldm@apartia.com; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: child table doesn't inherit PRIMARY KEY?
@ 2001-01-19 17:37 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2001-01-19 17:37 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-general@postgresql.org
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
@ 2001-01-24 13:44 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-01-24 13:44 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
OK, what do people want to do with this item? Add to TODO list?
Seems making a separat unique constraint would be easy to do and be of
value to most users.
>
> 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?
>
>
--
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
@ 2001-01-24 19:25 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Stephan Szabo @ 2001-01-24 19:25 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
On Wed, 24 Jan 2001, Bruce Momjian wrote:
>
> OK, what do people want to do with this item? Add to TODO list?
>
> Seems making a separat unique constraint would be easy to do and be of
> value to most users.
The problem is that doing that will pretty much guarantee that we won't
be doing foreign keys to inheritance trees without changing that behavior
and we've seen people asking about adding that too. I think that this
falls into the general category of "Make inheritance make sense" (Now
there's a todo item :) ) Seriously, I think the work on how inheritance
is going to work will decide this, maybe we end up with a real inheritance
tree system and something that works like the current stuff in which case
I'd say it's probably one unique for the former and one per for the
latter.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
@ 2001-01-24 19:31 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2001-01-24 19:31 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
> On Wed, 24 Jan 2001, Bruce Momjian wrote:
>
> >
> > OK, what do people want to do with this item? Add to TODO list?
> >
> > Seems making a separat unique constraint would be easy to do and be of
> > value to most users.
>
> The problem is that doing that will pretty much guarantee that we won't
> be doing foreign keys to inheritance trees without changing that behavior
> and we've seen people asking about adding that too. I think that this
> falls into the general category of "Make inheritance make sense" (Now
> there's a todo item :) ) Seriously, I think the work on how inheritance
> is going to work will decide this, maybe we end up with a real inheritance
> tree system and something that works like the current stuff in which case
> I'd say it's probably one unique for the former and one per for the
> latter.
I smell TODO item. In fact, I now see a TODO item:
* Unique index on base column not honored on inserts from inherited table
INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
[inherit]
So it seems the fact the UNIQUE doesn't apply to the new table is just a
manifestion of the fact that people expect UNIQUE to span the entire
inheritance tree. I will add the emails to [inherit] and mark it as
resolved.
--
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-01-24 19:31 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-03 15:22 child table doesn't inherit PRIMARY KEY? Louis-David Mitterrand <cunctator@apartia.ch>
2000-06-03 15:51 ` Louis-David Mitterrand <cunctator@apartia.ch>
2000-06-04 01:46 ` Peter Eisentraut <peter_e@gmx.net>
2000-06-04 07:20 ` Louis-David Mitterrand <cunctator@apartia.ch>
2000-10-02 16:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-01-19 04:57 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-01-19 17:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-24 13:44 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-01-24 19:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-24 19:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
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