Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres FK referencing composite PK

Tags:

postgresql

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"?

like image 282
punkish Avatar asked Apr 02 '12 21:04

punkish


People also ask

Can a foreign key references composite primary key?

Is it possible to use one of the attributes of a composite primary key as a foreign key? Yes, this is quite common and perfectly valid. The best example comes from the classic many-to-many relationship.

What is composite primary key in PostgreSQL?

A primary key is a field assigned to a row with unique values in a database's table, but when a table has more than one unique value, we use a composite primary key for these attributes that will set them apart for distinction. PostgreSQL allows its users to have composite primary keys in their tables.

Can a foreign key reference multiple columns?

MySQL allows us to add a FOREIGN KEY constraint on multiple columns in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.

How can I list all foreign keys referencing a given table in PostgreSQL?

To get a list of all foreign keys of the table using psql you can use the \d your_table_name command line.


2 Answers

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)
);
like image 96
Mike Sherrill 'Cat Recall' Avatar answered Oct 17 '22 08:10

Mike Sherrill 'Cat Recall'


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)
);
like image 24
Dave Halter Avatar answered Oct 17 '22 07:10

Dave Halter