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[].

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match