Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question on Inheritance and Primary Keys.

Question on Inheritance and Primary Keys.

From
Dutt Kalapatapu
Date:
I have two Tables:

TableA(
parameterone int,
parametertwo int,
PRIMARYKEY(parameterone),
UNIQUE(parameterone)
);

TableB(
parameterthree int
)INHERITS(TableA);


I expect from this that when we insert some thing in TableB then
parameterone should be unique, but the database is not enforcing the rule
and it takes duplicate values.  But when I insert two duplicate keys in
TableA then it complains.  This means that Inheritance removes the
primarykey etc from the inherited table??? I couldn't think why this object
oriented behaviour doesn't work here.


Can some one explain this behaviour.

Thanks
Dutt

--Dutt Kalapatapu   ________________________  |ipVerse Inc.,           |  |1901 Landings Drive     |  |Mountain View,
CA-94043|  |Telefono: 650 919 0647  |  |________________________|

Re: [SQL] Question on Inheritance and Primary Keys.

From
Tom Lane
Date:
Dutt Kalapatapu <dutt@eudoramail.com> writes:
> I expect from this that when we insert some thing in TableB then
> parameterone should be unique, but the database is not enforcing the rule
> and it takes duplicate values.

Constraints and indexes aren't inherited; you have to redeclare them for
each derived table.  I agree this is a bug, or at least lack of a
feature that ought to be there.  It's on the TODO list, in fact:

* Unique index on base column not honored on inserts from inherited table INSERT INTO inherit_table (unique_index_col)
VALUES(dup) should fail [inherit] 
 

I don't know when it might get fixed.  None of the currently active
developers seem to be very interested in inheritance issues, so it
could be a while before anyone decides to look at this problem.
        regards, tom lane

Re: [SQL] Question on Inheritance and Primary Keys.

From
Dutt Kalapatapu
Date:


If I redeclare the Values again, will it overwrite the inherited values?

Thanks for the clarification.
-Dutt

At 09:21 PM 10/11/99 -0400, Tom Lane wrote:
>Dutt Kalapatapu <dutt@eudoramail.com> writes:
>> I expect from this that when we insert some thing in TableB then
>> parameterone should be unique, but the database is not enforcing the rule
>> and it takes duplicate values.
>
>Constraints and indexes aren't inherited; you have to redeclare them for
>each derived table.  I agree this is a bug, or at least lack of a
>feature that ought to be there.  It's on the TODO list, in fact:
>
>* Unique index on base column not honored on inserts from inherited table
>  INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
>  [inherit] 
>
>I don't know when it might get fixed.  None of the currently active
>developers seem to be very interested in inheritance issues, so it
>could be a while before anyone decides to look at this problem.
>
>            regards, tom lane
>
--Dutt Kalapatapu   ________________________  |ipVerse Inc.,           |  |1901 Landings Drive     |  |Mountain View,
CA-94043|  |Telefono: 650 919 0647  |  |________________________|

Re: [SQL] Question on Inheritance and Primary Keys.

From
Tom Lane
Date:
What you declare for the derived table doesn't affect the parent,
if that's what you meant.
        regards, tom lane