ptsd triggered ptsd triggered - 1 year ago 129
MySQL Question

Mysql - Make combination of two columns unique

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

column and
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


Answer Source

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);


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