Can a multi-field UNIQUE constraint include fields from more than one table?

Question

I have the following 2 tables:

Bookings
  - User ID

Shifts
  - Booking ID
  - Date

I'd like to make sure that a user isn't booked in more than one shift on the same date. In other words, a UNIQUE constraint for fields [Bookings.UserID, Shifts.date]

Can I do this in PostgreSQL?

Answer 1

Indexes in PostgreSQL are on one table and cannot span multiple tables.

But you can solve your problem with proper tables layout. What you probably need is an n:m relationship, implemented with 3 tables. Then there are various ways to enforce your condition.

You can store the date in booking redundantly, create the primary key on (usr_id, shift_date) instead of (usr_id, shift_id) and guarantee referential integrity with a multi-column foreign key on (shift_id, shift_date):

CREATE TABLE usr(
  usr_id serial PRIMARY KEY
 --, more?
);

CREATE TABLE shift(
  shift_id serial PRIMARY KEY
, shift_date date
  --, more?
);

This also requires a UNIQUE INDEX on shift first:

CREATE UNIQUE INDEX shift_date_idx ON shift (shift_id, shift_date);

CREATE TABLE booking(
  usr_id int REFERENCES usr(usr_id)
, shift_id int
, shift_date date
  --, more?
, CONSTRAINT booking_pkey PRIMARY KEY (usr_id, shift_date)
, CONSTRAINT booking_fkey FOREIGN KEY (shift_id, shift_date)
 REFERENCES shift(shift_id, shift_date)
);

Another, less restrictive, but also less reliable way would be a trigger ON INSERT OR UPDATE on the table booking that checks if the user has a shift with the new date already. Could work without the redundant date column.

I would go with the first solution.

---

There are no indexes on views in PostgreSQL either.

You could create a function that returns shift_date for a given shift_id and declare it IMMUTABLE (although, in fact, it isn't). With that you could create a multi-column index on an expression:

CREATE function f_shift_date (shift_id int)
  RETURNS date
  LANGUAGE sql IMMUTABLE AS
$func$
   SELECT shift_date FROM shift WHERE shift_id = $1;
$func$;

CREATE UNIQUE INDEX shift_date_idx ON booking (shift_id, f_shift_date(shift_id));

This would enforce your condition. But honestly, this is pretty suicidal. The index relies on the condition that you get the same date for a shift_id indefinitely. If shift_date ever changes relative to its shift_id, the index has to be recreated or it will yield incorrect results.
So, don't do that.