sql - Mysql Multiple Unique Keys Across Tables -

i have table 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 user can't arrange appointment doctor more once in day. want add unique constraint between doctor_id , user_id in structure can't. tried things 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 know, didn't work. there suggestions can make?

based on comment doctor_slot is, seem have bit on issue schema design. there should no reason store both slot_id , date in appointment table, in doctor_slot has date component, storing date in appointment table redundant storage of data, , become problematic keep in sync.

of course, without date on table impossible force unique constraint in database table.

my recommendation type of calendar-based app this, first create date table. use script 1 here: http://www.dwhworld.com/2010/08/date-dimension-sql-scripts-mysql/ create date table. having such table can allow use simple date_id reference kinds of different information date (this technique commonly used in data warehouses). long use date_id in other tables need dates, extremely simple of dates in fashion desire (by day of week, month, week number, whether weekday or not, etc.).

you use similar concept build timeslots. maybe make table has 96 entries (24 hours * 15 minutes) represent 15 minute intervals - can change whatever interval like.

you build appointment table this:

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

id don't see separate need doctor_slots table here. if want track open doctor slots, in table having user_id = null until slot filled.

this allow enforce unique index on user_id , date_id.