0

Using a foreign key to connect two simple tables is straight forward in PostgreSQL:

CREATE TABLE location (
    id UUID PRIMARY KEY,
    name TEXT
);

CREATE TABLE event (
    id UUID PRIMARY KEY,
    location_id UUID,
    title TEXT,
    FOREIGN KEY(location_id) REFERENCES location(id)
);

INSERT INTO location VALUES ('41af871f-a939-46d1-8cac-dd8489ca3248', 'Jupiter');
INSERT INTO event VALUES ('56ab1bf0-36bc-4380-a9d2-fccb88f5ca03', '41af871f-a939-46d1-8cac-dd8489ca3248', 'Success');
-- the next insert statement is supposed to fail
INSERT INTO event VALUES ('da5ecc5f-d63f-4803-883f-70cda9da7583', '529d1030-0db1-4299-8557-055471820b66', 'Failure');

The last statement fails as expected, since there is no location with id = 529d1030-0db1-4299-8557-055471820b66

Now my actual location table is slightly more complex, because it uses SCD Type 2 with date columns:

CREATE TABLE location (
    id UUID PRIMARY KEY,
    name TEXT
    checkin TIMESTAMP NOT NULL,
    checkout TIMESTAMP, -- is null for the latest/current version
);

CREATE UNIQUE INDEX "unique_location" ON location (id, (checkout IS NULL)) WHERE checkout IS NULL;

The location table can now contain multiple versions of a location with a certain id. Only 0 or 1 versions are valid for a given time. Outdated versions have a timerange in the checkin andcheckout fields, while the checkout field of the latest or current version is set to NULL.

Now I want to have a foreign key in the event table, that refers to the current version of a location. A bit like the following (non-working) pseudocode:

CREATE TABLE event (
    -- field definitions
    FOREIGN KEY(location_id) REFERENCES location(id) WHERE location(checkout) IS NULL
);

Is this possible somehow and how?

Filchos
  • 71
  • 3
  • 1) You are using `timestamp` not `date` columns. FYI, save future headaches and use `timestamptz` instead. 2) I don't understand how *The location table can now contain multiple versions of a location with a certain id.* works when `id` is a PK? – Adrian Klaver Aug 31 '23 at 15:34
  • 2) `id` is not a PK. The combination of `id` and `checkout` is unique and an index is created on these fields. – Filchos Sep 01 '23 at 05:26
  • 1) timestamp always contains UTC datetime, not local datetime. @AdrianKlaver Do you think it is better to use `TIMESTAMPTZ` in this case as well? – Filchos Sep 01 '23 at 05:33
  • 1) `id UUID PRIMARY KEY,` says it is a PK. Therefore `CREATE UNIQUE INDEX "unique_location" ON location (id, (checkout IS NULL) ...` will not do what you think it is. 2) `timestamptz` values are stored as UTC. Then you know for certain. – Adrian Klaver Sep 01 '23 at 14:57
  • PostgreSQL requires that foreign key contraints reference unique keys. There are other database platforms that support temporal referential integrity. Tiggers and stored procedures can be used to implement the type of behavior you've described; however, it's not a trivial exercise, offers ample opportunities to introduce defects, and can add significant complexity to even relatively simple queries. PostgreSQL has some features, such as the `tsrange` data type and exclusion constraints using `GIST` indexes, that facilitate handling temporal constraints. – JohnH Sep 02 '23 at 16:58

0 Answers0