Re: SQL Database design question

You can add a cascading delete and a trigger:

CREATE TABLE EmployeeBranches (ee_code CHAR(10) NOT NULL REFERENCES Employees (ee_code) ON DELETE CASCADE, branch_id INTEGER NOT NULL REFERENCES Branches (branch_id), PRIMARY KEY (ee_code, branch_id))

CREATE TRIGGER trg_prevent_orphaned_employees ON EmployeeBranches FOR UPDATE, DELETE
AS
IF EXISTS
(SELECT *

FROM Employees AS E
LEFT JOIN EmployeeBranches AS B
ON E.ee_code = B.ee_code
WHERE B.ee_code IS NULL
AND E.ee_code IN
(SELECT ee_code

FROM Inserted
UNION ALL
SELECT ee_code
FROM Deleted))
BEGIN
ROLLBACK TRAN
RAISERROR('Orphaned employees not permitted',16,1) END The ON DELETE CASCADE option is required otherwise it wouldn't be possible to delete an employee.

Alternatively, I assume you don't allow your web app to modify these tables directly so you may prefer to do a similar check in the SPs that perform the updates and deletes.

-- 
David Portas
SQL Server MVP
--