PostgreSQL Bugs - BUG #1688: inheritance and foreign key creation problem

Author

BUG #1688: inheritance and foreign key creation problem

2005-05-31, 11:24 am

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
);

-----------

We can see that boss relation has __key__ column (inherited from employee
relation), but when executing above script PostgreSQL fails with message:

psql:script.sql:20: ERROR: there is no unique constraint matching given
keys for referenced table "boss"

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Stephan Szabo

2005-05-31, 11:24 am

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Sponsored Links