Re: BUG #1688: inheritance and foreign key creation problem

On Sun, 29 May 2005,  wrote:

>
> The following bug has been logged online:
>
> Bug reference:      1688
> Logged by:
> Email address:      wrobell@pld-linux.org
> PostgreSQL version: 8.0.3
> Operating system:   Linux
> Description:        inheritance and foreign key creation problem
> Details:
>
> -----------
> create table employee (
>     __key__      integer,
>     name         varchar(10) not null,
>     surname      varchar(20) not null,
>     phone        varchar(12) not null,
>     unique (name, surname),
>     primary key (__key__)
> );
>
> create table boss (
>     dep_fkey     integer unique
> ) inherits(employee);
>
>
> create table department (
>     __key__        integer,
>     boss_fkey  integer unique,
>     primary key (__key__),
>     foreign key (boss_fkey) references boss(__key__) initially deferred
> );

Unique constraints do not currently inherit, so right now, there is no
unique constraint on boss.__key__ and you'll be able to insert duplicates.
Right now, you can almost work around this by explicitly placing a unique
constraint on the column, however, that only guarantees uniqueness within
a table not between employee and boss.