Mysql Multiple Unique Keys Across Tables

Question

I have a table like that:

CREATE TABLE `Appointment` (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    doctor_slot_id INT NOT NULL,
    date DATE NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES user(id),
    FOREIGN KEY(doctor_slot_id) REFERENCES doctor_slot(id)
);

I want that a user can't arrange an appointment with a doctor more than once in a day. So I want to add a unique constraint between doctor_id and user_id but in this structure I can't. I tried those things which are not in SQL syntax:

UNIQUE(user_id, doctor_slot.doctor_id)

and

UNIQUE(user_id, doctor_slot(doctor_id))

and

UNIQUE(user_id, doctor_id(doctor_slot))

But as you know, they didn't work. Are there any suggestions you can make?

A user can arrange appointments for different slots. A slot is a tupple such that (day_of_week, hour, minute). But a user can't make an appointment to a doctor in the same day. — sedran, May 14 '13 at 21:03
can you explain what a doctor_slot_id represents? Is this a slot within a day (i.e. 9AM, 10AM, etc.) or is does it also have it's own date component (i.e. 9AM on different days would have different slot_id)? — Mike Brant, May 14 '13 at 21:03
@MikeBrant a doctor_slot is a table which holds (day_of_week, hour, minute). It's like Monday 9.50. You can arrange two appointment with a doctor in different days but not in the same day. — sedran, May 14 '13 at 21:05
Hm, in the current setup with no `doctor_id` in `Appointment`, and `Doctor_slot` apparently not being unique date/time combo's but a repeating schedule, about your only option is to create a cludgy [`ON BEFORE INSERT` trigger...](http://stackoverflow.com/questions/229765/triggers-that-cause-inserts-to-fail-possible) which makes the `INSERT` fail a bit... ehm.. obscurely/weird. — Wrikken, May 14 '13 at 21:29
Hm, apparently [in MySQL >=5.5 we can error in a more sane manner](http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger/7189396#7189396) — Wrikken, May 14 '13 at 21:30
Create a stored procedure to add an appointment, encapsulating the logic of this check? — Christopher Stevenson, May 15 '13 at 15:45
For an astonishingly easly solution using TRIGGER see https://dba.stackexchange.com/a/300104/21552 — BurninLeo, Sep 26 '21 at 06:17

score 0 · Accepted Answer · answered May 14 '13 at 21:26

Based on your comment about the what the doctor_slot is, it would seem you have a bit on an issue with your schema design. There should be no reason for you to store both a slot_id and a date in the appointment table, in that the doctor_slot already has a date component, so storing the date in the appointment table is a redundant storage of data, and could become problematic to keep in sync.

Of course, without the date on this table it is impossible to force a unique constraint in the database for this table.

My recommendation for any type of calendar-based app like this, would be to first create a date table. I usually use a script like the one here: http://www.dwhworld.com/2010/08/date-dimension-sql-scripts-mysql/ to create this date table. Having such a table can allow you to use a simple date_id to reference all kinds of different information about a date (this is a technique commonly used in data warehouses). As long as you use this date_id in all the other tables where you need dates, it as extremely simple to look of dates in any fashion you desire (by day of week, month, week number, whether it is a weekday or not, etc.).

You could use a similar concept to build your timeslots. Maybe make a table that has 96 entries (24 hours * 15 minutes) to represent 15 minute intervals - obviously you can change this to whatever interval you like.

You could then build your appointment table like this:

appointment_id
user_id
doctor_id
date_id
time_start_id <= time slot for appointment start
time_end_id <= time slot for appointment end

Id don't see separate need for a doctor_slots table here. If you want to track open doctor slots, you could also do that in this table by having user_id simply = NULL until the slot is filled.

This would allow you to enforce unique index on user_id and date_id.

Thanks for the answer. The reason why I created doctor_slot table is that there is slots that the hospital is open, and there are doctors who are working in different slots. Like Part Time doctors. So a user can arrange an appointment for only a doctor's available slot. — sedran, May 14 '13 at 21:36
And I have to keep my tables in BCNF, this is why I can't put doctor_slot_id and doctor_id together in the appointment table. — sedran, May 14 '13 at 21:37
@sedran I wasn't suggesting that you put the doctor_and the a doctor_slot_it in the same table, but rather the doctor and a simple slot_id (a slot not related to a particular doctor). Certainly the time slot start and end would not meet BCNF. I think you could remove doctor and slot info into separate table as you suggest and still use the concept of date_id and user_id unique key here. Slot id would still however need to not be date-specific, but rather be day of week and time specific (so the slot table may have more id's than I originally posed) — Mike Brant, May 14 '13 at 21:46

Mysql Multiple Unique Keys Across Tables

1 Answers1