Check constraint on table lookup
- by bzamfir
Hi,
I have a table, department , with several bit fields to indicate department types
One is Warehouse (when true, indicate the department is warehouse)
And I have another table, ManagersForWarehouses with following structure:
ID autoinc
WarehouseID int (foreign key reference DepartmentID from departments)
ManagerID int (foreign key reference EmployeeID from employees)
StartDate
EndDate
To set new manager for warehouse, I insert in this table with EndDate null, and I have a trigger that sets EndDate for previous record for that warehouse = StartDate for new manager, so a single manager appears for a warehouse at a certain time.
I want to add two check constraints as follows, but not sure how to do this
do not allow to insert into ManagersForWarehouses if WarehouseID is not marked as warehouse
Do not allow to uncheck Warehouse if there are records in ManagersForWarehouses
Thanks