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"?
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.
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.
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.
To get a list of all foreign keys of the table using psql you can use the \d your_table_name command line.
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)
);
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)
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With