Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

example

+----+-----+-----+--------+----------+
| 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) 
or 
row[3](pid => 0, car => 5)

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

Thanks

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
1  
So, (first delete the bad data, then) define 2 unique constraints! –  ypercube Feb 26 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 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 at 2:03

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.