I have a table in a PostgreSQL DB like this to represent a booking system for office desks. Employees can book one desk (seat_id) per day.

How to make a unique constraint with multiple column (booked_date, seat_id, and status with value 'booked') to guarantee that no more than one employee can book the same seat for the same day?

Note: employees can cancel the seat that they have booked and the row stays in the table with changed status.

CodePudding user response：

Use a partial UNIQUE index:

CREATE UNIQUE INDEX ON booking_tbl (booked_date, seat_id)
WHERE status = 'booked';

Related:

• Create unique constraint with null columns