1

I am trying to create a scheduling application, and part of it requires the date range of a row to be within the date range of a row in another table, which it is referencing via a foreign key.

Note: This is Postgres.

The schema is as follows:

table_1:
id Serial
name Varchar
date_range DATERANGE
table_2:
id Serial
name Varchar
date_range DATERANGE
table_1_id - Foreign Key, this references the id of a record in table 1.

I am trying to figure out how to write a check constraint that enforces the rule that the date_range in table_2 has to be within the date_range of table_1.

1
  • Search for exclusion constraints; there were several good answers on this site alone.
    – mustaccio
    Feb 8 at 22:50

1 Answer 1

0

There is no really good way to do this. The only option I can think of is to hold a redundant copy of table_1's date range in table_2 and proceed like this (omitting non-essential columns for brevity):

CREATE TABLE table_1 (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   date_range daterange NOT NULL,
   /* this redundant unique constraint is required
      as target for a foreign key that ensures consistency */
   UNIQUE (date_range, id)
);

CREATE TABLE table_2 (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   date_range daterange NOT NULL,
   table_1_id bigint NOT NULL,
   /* this column holds a redundant copy from table_1 */
   table_1_date_range daterange NOT NULL,
   /* this foreign key makes sure that the date range is
      consistent consistent with table_1 */
   FOREIGN KEY (table_1_date_range, table_1_id) REFERENCES table_1 (date_range, id),
   /* this constraint ascertains your desired consition */
   CHECK (date_range <@ table_1_date_range)
);

Ugly, isn't it? I'd do without enforcing the constraint like this, the overhead is just too high.

You have to resist the temptation to use a dirty trick like defining a check constraint that calls a function that selects from another table. That is explicitly forbidden and will lead to trouble, see for example this thread.

A viable alternative solution is to use a trigger that checks the constraint upon every data modification, but such triggers always suffer from race conditions, so you'd have to use heavy locking or SERIALIZABLE transaction isolation to make them work reliably.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.