Re: SQL Database design question

Dan Williams wrote:
> I need to ensure that every employee created has and maintains at
least one
> entry in the EmployeeBranches table.

I don't know much about SQL Server. I presume it does not permit complex check constraints like this?:

ALTER TABLE Employees ADD CONSTRAINT eb_chk CHECK (EXISTS (SELECT NULL FROM Employee_Branches eb WHERE eb.EE_Code = e.EE_Code));

It would need to be a DEFERRED constraint. A deferred constraint is not checked until you commit, so it allows you to enter a new Employee row without hitting a constraint violation right away because you haven't inserted any EmployeeBranches rows yet!

If that doesn't work, another approach is to use triggers to maintain a Branch_Count column in the Employees table. I don't know SQL Server syntax, but the pseudocode would be something like:

on insert of Employees:
set Branch_Count = (select count(*) from Employee_Branches where EE_Code = ...)

after insert of EmployeeBranches:
update Employees set Branch_Count = Branch_Count+1 where EE_Code = ...

after delete of EmployeeBranches:
update Employees set Branch_Count = Branch_Count-1 where EE_Code = ...

Then you would need a DEFERRED check constraint on Employees: check(Branch_Count > 0). Received on Fri Jan 28 2005 - 13:35:16 CET