Re: Array of foreign key - Mailing list pgsql-general
On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
I don't think you understand how Foreign Key constraints work in PostgreSQL.PostgreSQL will prevent any insert where the value of a column is not within the FK table.So you DO NOT need a check constraint or trigger.I thought the OP was asking for this feature ("Support for Array ELEMENT Foreign Keys"):which would be super-handy, but doesn't actually exist. You can enforce it yourself with a trigger on both tables, but I still hope this someday gets included into Postgres!Ken
Actually, I think the op may be referring to a MULTI COLUMN FK array
EG:
DROP TABLE drivers
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
driver_first_name text,
driver_last_name text,
CONSTRAINT drivers_uq UNIQUE (driver_first_name, driver_last_name)
);
CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
driver_first_name text,
driver_last_name text,
final_positions integer,
CONSTRAINT races_driver_fk FOREIGN KEY (driver_first_name, driver_last_name)
REFERENCES drivers (driver_first_name, driver_last_name)
);
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
driver_first_name text,
driver_last_name text,
CONSTRAINT drivers_uq UNIQUE (driver_first_name, driver_last_name)
);
CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
driver_first_name text,
driver_last_name text,
final_positions integer,
CONSTRAINT races_driver_fk FOREIGN KEY (driver_first_name, driver_last_name)
REFERENCES drivers (driver_first_name, driver_last_name)
);
and that is available,
which is why I requested clarification.
So I guess we will have to wait for the op's response.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: