How do I check constraints between two tables when inserting into a third table that references the other two tables?

Question

Hello

Consider this example schema:

Customer ( int CustomerId pk, .... )

Employee ( int EmployeeId pk,
           int CustomerId references Customer.CustomerId, .... )

WorkItem ( int WorkItemId pk,
           int CustomerId references Customer.CustomerId,
           null int EmployeeId references Employee.EmployeeId, .... )

Basically, three tables:

• A customer table with a primary key and some additional columns
• A employee table with a primary key, a foreign key constraint reference to the customer tables primary key, representing an employee of the customer.
• A work item table, which stores work done for the customer, and also info about the specific employee who the work was performed for.

My question is. How do I, on a database level, test if an employee is actually associated with a customer, when adding new work items.

If for example Scott (employee) works at Microsoft (customer), and Jeff (employee) works at StackOverflow (customer), how do I prevent somebody from adding a work item into the database, with customer = Microsoft, and employee = Jeff, which do not make sense?

Can I do it with check constraints or foreign keys or do I need a trigger to test for it manually?

Should mention that I use SQL Server 2008.

UPDATE: I should add that WorkItem.EmployeeId can be null.

Thanks, Egil.

Answer 1

Wouldn't a foreign key on a composite column (CustomerId, EmployeeId) work?

ALTER TABLE WorkItem
ADD CONSTRAINT FK_Customer_Employee FOREIGN KEY (CustomerId, EmployeeId)
    REFERENCES Employee (CustomerId, EmployeeId);

Answer 2

Either:

• make the EmployeeID column the Primary Key of Employee (and possibly an auto-id) and store the EmployeeID in the WorkItem record as a foreign key, instead of storing the Employee and Customer IDs in WorkItem. You can retrieve a WorkItem's Customer details by joining to the Customer table via the Employee table.

Or:

• make the WorkItem's EmployeeID and CustomerID columns a composite foreign key to Employee.

I favour the first approach, personally.

Answer 3

Why do you want employeeId to be null int WorkItem? Maybe you should add another table to avoid that particular oddity. From what I can see the easiest thing to do is to add a unique constraint on employeeid in workItem, and maybe even unique on customerId if that is what you want.

A more general way to add constraints spanning many tables is to define a view that should always be empty, and add the constraint that it is empty.

Answer 4

You might be able to do this by creating a view "WITH SCHEMABINDING" that spans those tables and enforces the collective constraints of the individual tables.

Answer 5

What are you trying to model here?

1. You're a contracting agency or the like, and you have a bunch of contractors who are (for some period of time) assigned to a customer.

2. You're actually storing information about other company's employees (maybe you're providing outsources payroll services, for example).

In case (1), it looks like you have a problem with the Employee table. In particular, when Scott's contract with MS is up and he gets contracted to someone else, you can't keep the historical data, because you need to change the CustomerId. Which also invalidates all the WorkItems. Instead, you should have a fourth table, e.g., CustomerEmployee to store that. Then WorkItem should reference that table.

In case (2), your primary key on Employee should really be CustomerId, EmployeeId. Two customers could have the same employee ID number. Then Kieron's foreign key will work.