Postgres FK referencing composite PK

Question

Consider

CREATE TABLE foo (
    id SERIAL,
    foo_created_on ABSTIME,
    foo_deactivated_on ABSTIME,
    PRIMARY KEY (id, foo_created_on)
);

CREATE TABLE bar (
    id SERIAL,
    bar_created_on ABSTIME,
    bar_deactivated_on ABSTIME,
    foo_id REFERENCES ( .. what goes here? ..),
    PRIMARY KEY (id, bar_created_on)
);

How do I create an FK in "bar" that references the PK in "foo"?

Btw: the "ABSTIME" data type is de-supported. You should use DATE or TIMESTAMP instead. — , Apr 02 '12 at 22:36

score 90 · Answer 1 · edited Feb 17 '19 at 21:00

How do I create an FK in "bar" that references the PK in "foo"?

With your current structure, you can't.

The target of a foreign key reference has to be declared either PRIMARY KEY or UNIQUE. So either this

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    foo_created_on ABSTIME,
    foo_deactivated_on ABSTIME,
    UNIQUE (id, foo_created_on)
);

or this

CREATE TABLE foo (
    id SERIAL,
    foo_created_on ABSTIME,
    foo_deactivated_on ABSTIME,
    PRIMARY KEY (id, foo_created_on),
    UNIQUE (id)
);

would work as a target for bar.foo_id. Then bar would have a simple reference.

CREATE TABLE bar (
    id SERIAL,
    bar_created_on ABSTIME,
    bar_deactivated_on ABSTIME,
    foo_id REFERENCES foo (id),
    PRIMARY KEY (id, bar_created_on)
);

If you want to reference the primary key you originally declared in foo, you have to store that primary key in bar. You have to store all of it, not part of it. So without modifying foo, you could build bar like this.

CREATE TABLE bar (
    id SERIAL,
    bar_created_on ABSTIME,
    bar_deactivated_on ABSTIME,
    foo_id INTEGER NOT NULL,
    foo_created_on ABSTIME NOT NULL,
    FOREIGN KEY (foo_id, foo_created_on) REFERENCES foo (id, foo_created_on),
    PRIMARY KEY (id, bar_created_on)
);

score 26 · Answer 2 · edited Feb 07 '18 at 00:56

26

You have to create separate foreign keys:

CREATE TABLE bar (
  id SERIAL,
  bar_created_on ABSTIME,
  bar_deactivated_on ABSTIME,
  foo_id INT,
  FOREIGN KEY (foo_id, created_on) REFERENCES foo (id, created_on),
  PRIMARY KEY (id, bar_created_on)
);

edited Feb 07 '18 at 00:56

pstanton

35,033
24
126
168

answered Apr 02 '12 at 21:24

Dave Halter

15,556
13
76
103

sorry, not clear... (I should have given the "created_on" column distinctive names to disambiguate... see the updated question now). Is the above incantation in "bar" schema? Keep in mind, bar's created_on tracks created_on for bar entries, not foo entries. – punkish Apr 02 '12 at 21:31
I updated the answer. I don't know what your foo_id was for, therefore I just made it an int. References are pretty easy, but maybe I don't understand what you mean. – Dave Halter Apr 02 '12 at 22:11
@Dave Perhaps what OP means is you've not listed `created_on` as a field of `bar`, yet you list it as part of the composite foreign key in `FOREIGN KEY( ... )`. On a different note, to disambiguate between the two `created_on`s, in the updated question, OP has added respective prefix. – legends2k Jun 07 '23 at 18:09

Postgres FK referencing composite PK

2 Answers2

Linked