Doğuş Yalçın Doğuş Yalçın - 5 months ago 23
SQL Question

How can I update a table FK column with unique value in SQL Server?

I have two tables


table has a
column. I choose managerID from
and add to
but no one can be manager of different departments at the same time. How can I control it in an update query?

Departments table

enter image description here

Workers table

enter image description here


If you want to block managers from being manager for more than one department than the easiest way is to create an unique index on the Departments table on the column ManagerID

create unique nonclustered index UX_ManagerID on Departments (ManagerID)
where ManagerID is NOT NULL;

Now all updates and inserts that attempt to make a manager the boss of more than one department will fail. In the error message the name 'UX_ManagerID' will be present so you can check that in the exception message and give a nice message to the user.