key - MySQL uniqueness of value -

i have table contains columns:

employer_id contact_id primary 

first 2 integers, last tinyint that's either 1/0. employer can joined multiple contacts , vice versa. however, want enforce there can 1 instance primary = 1 each particular employer. there can many primary = 0.

is there way through use of constraints/keys in mysql?

thanks

not through keys, can set insertion/update trigger check (count(*) primary=1 , employer_id=?) <= 1.

edit: actually, think there way: make second table employer_id primary key , second field primary contact. you'd have these 2 tables:

===================================   ================================ |          maintable              |   |       maincontacttable       | ===================================   ================================ |employer_id|contact_id|...data...|   |employer_id|primary_contact_id| |   pk      |    fk    |          |   |    pk fk  |       fk         | ===================================   ================================ 

no more primary column. since employer_id key in second table, can have @ 1 entry.

you primary contact through left outer join , returns either primary contact or null.