Thread: BUG #2138: Feature request: handle foreign key constraints on arrays
BUG #2138: Feature request: handle foreign key constraints on arrays
From
"Bernhard Rosenkraenzer"
Date:
The following bug has been logged online: Bug reference: 2138 Logged by: Bernhard Rosenkraenzer Email address: bero@arklinux.org PostgreSQL version: 8.1.1 Operating system: Linux (Ark Linux 2005.2) Description: Feature request: handle foreign key constraints on arrays Details: Best illustrated by an example: A company has several offices and some employees who work in office #1 some of the time, and in office #2 at a different time. It would be nice to represent them in the database like this: CREATE TABLE offices(id SERIAL8 UNIQUE PRIMARY KEY, street VARCHAR(128)); CREATE TABLE employees(id SERIAL8 UNIQUE PRIMARY KEY, name VARCHAR(128), workplace BIGINT[] REFERENCES offices(id) ON UPDATE CASCADE); Currently postgres refuses to do this because BIGINT[] and BIGINT are different types, it would be nice to simply apply the constraint to all entries in the array if a constraint for type X is applied to X[].
On 1/2/06, Bernhard Rosenkraenzer <bero@arklinux.org> wrote: > > The following bug has been logged online: > > Bug reference: 2138 > Logged by: Bernhard Rosenkraenzer > Email address: bero@arklinux.org > PostgreSQL version: 8.1.1 > Operating system: Linux (Ark Linux 2005.2) > Description: Feature request: handle foreign key constraints on > arrays > Details: > > Best illustrated by an example: > an example of what *NOT* to do? > A company has several offices and some employees who work in office #1 so= me > of the time, and in office #2 at a different time. > > It would be nice to represent them in the database like this: > CREATE TABLE offices(id SERIAL8 UNIQUE PRIMARY KEY, street VARCHAR(128)); UNIQUE and PRIMARY KEY are the same... > CREATE TABLE employees(id SERIAL8 UNIQUE PRIMARY KEY, name VARCHAR(128), > workplace BIGINT[] REFERENCES offices(id) ON UPDATE CASCADE); > what you should do is: CREATE TABLE offices(id SERIAL8 PRIMARY KEY, street VARCHAR(128)); CREATE TABLE employees(id SERIAL8 UNIQUE PRIMARY KEY, name VARCHAR(128)); CREATE TABLE emp_offices (employee bigint not null references employees, office bigint not null references offices, PRIMARY KEY(employee, office)); > Currently postgres refuses to do this because BIGINT[] and BIGINT are > different types, it would be nice to simply apply the constraint to all > entries in the array if a constraint for type X is applied to X[]. > > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)