Search code examples
sqlpostgresqlconstraintsunique

Unique constraint for certain value

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.

ID Booked_Date Seat_ID Employee_ID Status
1 2022-07-08 10C id1 booked
2 2022-07-08 20C id2 cancelled

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.

Solution

  • Use a partial UNIQUE index:

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

    Related: