Tengo una tabla de citas con esta estructura (fragmentada para facilitar la lectura):
appointments: - id - staff_id - start_time - end_time - cancelledQuiero agregar una restricción de base de datos para no poder duplicar citas de reserva. Me preguntaba si era posible agregar una restricción a lo largo de las líneas de:
when staff_id = ? and cancelled = false then set a "unique" constraint on start_time
Si esto no es posible, ¿hay algo similar que pueda hacer para lograr mi objetivo final?
esta es la tabla de citas completa
CREATE TABLE "appointments" ( "id" uuid, "customer_id" uuid NOT NULL REFERENCES customers ON DELETE CASCADE ON UPDATE CASCADE, "staff_id" uuid NOT NULL REFERENCES staff ON DELETE CASCADE ON UPDATE CASCADE, "start_time" timestamp NOT NULL, "end_time" timestamp NOT NULL, "notes" text, "cancelled" boolean NOT NULL DEFAULT false, "created_at" timestamp with time zone NOT NULL, "updated_at" timestamp with time zone NOT NULL, );Con la exclusión:
CREATE TABLE "appointments" ( "id" uuid, "customer_id" uuid NOT NULL REFERENCES customers ON DELETE CASCADE ON UPDATE CASCADE, "staff_id" uuid NOT NULL REFERENCES staff ON DELETE CASCADE ON UPDATE CASCADE, "start_time" timestamp NOT NULL, "end_time" timestamp NOT NULL, "notes" text, "cancelled" boolean NOT NULL DEFAULT false, "created_at" timestamp with time zone NOT NULL, "updated_at" timestamp with time zone NOT NULL, EXCLUDE USING gist ( staff_id WITH =, tsrange(start_time, end_time) WITH && ) WHERE (NOT cancelled), PRIMARY KEY ("id") );Ejecutando con error de exclusión:
data type uuid has no default operator class for access method "gist"
Necesita una restricción de exclusión para detener la doble reserva de citas. El método en la respuesta elegida solo evita que dos citas tengan la misma hora de inicio. No evita que una cita se superponga si comienza después de la primera cita.
CREATE TABLE appointments ( id serial PRIMARY KEY, staff_id int, start_time timestamp, end_time timestamp, cancelled bool DEFAULT false, EXCLUDE USING gist ( staff_id WITH =, tsrange(start_time, end_time) WITH && ) WHERE (NOT cancelled) );Ahora no puede duplicar citas de reserva.
INSERT INTO appointments (staff_id, start_time, end_time) VALUES ( 1, '01-01-2010T07:30', '01-01-2010T09:30' ), ( 1, '01-01-2010T08:00', '01-01-2010T09:45' ) ; ERROR: conflicting key value violates exclusion constraint "appointments_staff_id_tsrange_excl" DETAIL: Key (staff_id, tsrange(start_time, end_time))=(1, ["2010-01-01 08:00:00","2010-01-01 09:45:00")) conflicts with existing key (staff_id, tsrange(start_time, end_time))=(1, ["2010-01-01 07:30:00","2010-01-01 09:30:00")). Alternativamente, puede eliminar start_time y end_time y ponerlos todos como rangos de marca de tiempo
create unique index the_index on appointments (staff_id, start_time) where not cancelled;