Doğuş Yalçın Doğuş Yalçın - 1 month ago 7
SQL Question

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

I have two tables

Departments
and
Workers
.

Departments
table has a
ManagerID
column. I choose managerID from
Workers.ID
and add to
Departments.ManagerID
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

Answer

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.