ptsd triggered ptsd triggered - 4 months ago 22
MySQL Question

Mysql - Make combination of two columns unique

i have a mysql database table with two columns of importance, the

university_id
column and
username
column. Here the university_id should be unique to the whole table, but the two columns can repeat many times but the same combinations must not repeat, the username should be unique only to the university_id. here is what i mean

id university_id username
1 10001 DrDre
2 10002 slimshady
3 10003 slimshady
4 10004 DrDre
5 10005 lilwyn
6 10005 lilwyn <- only this line would be illegal
7 10005 kanyeEast



  • if possible i would also like to know how to do it using doctrine in symfony2



-thanks

Answer

use below alter statement if you did not make any constraint on table-

alter table mytable 
add unique key Uk_university_id(university_id), 
add unique key Uk_unvid_username(university_id,username);

Update:

alter table mytable  
add unique key Uk_unvid_username(university_id,username);
Comments