Doğuş Yalçın Doğuş Yalçın - 1 year ago 81
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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download