Geek asks and answers

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

I have the following two tables:

Bookings
  - User ID

Shifts
  - Booking ID
  - Date

I would like to make sure that the user is not booked in more than one shift on the same date. In other words, the UNIQUE constraint for the fields [Bookings.UserID, Shifts.date]

Can I do this in PostgreSQL?

+5
source share
2 answers

Indexes in PostgreSQL are in the same table and cannot span multiple tables.

But you can solve your problem with the correct layout of tables. You probably need an n: m relationship implemented with three tables. Then there are various ways to enforce your condition.

  • , (usr_id, shift_date) (usr_id, shift_id) (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?
);

UNIQUE INDEX on shift:

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)
);
  • , , ON INSERT OR UPDATE booking, , . .

.

PostgreSQL ( , 9.1).

, shift_date shift_id IMMUTABLE ( ). :

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

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

. , , . , shift_id . shift_date - shift_id, . , .

+7

?

ALTER TABLE shifts
ADD CONSTRAINT uk_shifts_book UNIQUE (booking_id, date);

, ( ) .

0

More articles:


All Articles