Make one field unique depending on another field

Question

I have the following MySQL table..

userid - foreign key
date   - primary key
slot1  - 1st free slot
slot2  - 2nd free slot
slot3  - 3rd free slot
slot4  - 4th free slot
slot5  - 5th free slot
slot6  - 6th free slot
slot7  - 7th free slot
slot8  - 8th free slot

All the slots are for the same day, so the user can input different times of the day that he is free.

Now I did this and it worked and now I stumbled upon a problem. I entered some data for one user and now when I try to enter data for another user for the same date I cant because it says it already exists.

How do i make it so that I can have the same user enter multiple dates and also another user enters the same date?... But the same user can't enter the same date twice... How can this be done?

Answer 1

You may want to use a composite PRIMARY KEY on user_id and date.

You may also use a unique key as Quassnoi suggested, but it really looks like your date field alone is not a valid candidate for a primary key.

Answer 2

ALTER TABLE mytable ADD CONSTRAINT ux_mytable_user_date UNIQUE KEY (user, date)

You should drop you PRIMARY KEY on date.

You may either create one on user_id, date (as Daniel Vassallo suggested), or, if you need to reference this table, create a surrogate PRIMARY KEY (say, with an AUTO_INCREMENT column).