Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I must enforce uniqueness within my table, But I cannot seem to find anywhere online(mysql manual,forums,stack overflow) talk about two unique constraints within one table.

Why you might ask I need two?


| PK | pid | car | serial | created  |
| 1  |  0  |  2  | 88888  | 02/04/12 | -> good data
| 2  |  0  |  2  | 22222  | 02/02/12 | -> bad data
| 3  |  0  |  5  | 88888  | 02/08/12 | -> bad data

pid and car must be unique and appear only once. pid and serial must be unique and appear only once.

At first I thought to add a constraint UNIQUE (pid,car,color), but with this constraint we could still enter false data as in the example,

row[2](pid => 0, serial => 22222) 
row[3](pid => 0, car => 5)

In other words, car or serial can only be inserted once for any one pid.


PS, Sorry if my explanation isnt so clear, I will have to re illustrate, possibly by finding a better example too.

share|improve this question
So, (first delete the bad data, then) define 2 unique constraints! – ypercubeᵀᴹ Feb 26 '14 at 0:10
I wasnt aware I could use 2 unique constraints? I will retest now, if it does work, how can I mark your answer as the correct seeing as your comment is direct reply. – cecilli0n Feb 26 '14 at 0:15
Thanks ypercube, two constraints did work, if you are happy to insert a dummy sql query with two unique constraints I would happillyy accept that as the answer. – cecilli0n Feb 26 '14 at 2:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.