How to add a constraint for multiple columns

Question

I added a constraint to a table so that the users cannot insert duplicate records for employee_nbr.

ALTER TABLE GamePresenterDB.gp.player_objects
    ADD CONSTRAINT AK_UniqueName UNIQUE (employee_nbr); 

This works fine, but I realize now that an employee number is associated with a group_id. So, the table can have duplicate employee_nbr column values as long as it is associated with a different group_id column.

How do I add a constraint so that the user is unable to enter a duplicate employee_nbr for the same group_id? My primary key in the table is a different identity column.

Answer 1

You should make the combination of group_id and employee_nbr unique:

ALTER TABLE GamePresenterDB.gp.player_objects
ADD CONSTRAINT AK_UniqueName UNIQUE (group_id, employee_nbr);

(and of course, drop the old constraint)