How to check the restrictions between two tables when pasting into a third table that refers to two other tables?

Question

Consider this example diagram:

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:

• Client table with primary key and a few additional columns
• Employee table with primary key, a reference to the foreign key constraint for the primary key of the customer table, representing the customer employee.
• The table of work items in which the work performed for the client is stored, as well as information about the specific employee for whom the work was performed.

My question is. As I, at the database level, I check whether the employee is really connected with the client when adding new work items.

, , () Microsoft (), Jeff () StackOverflow (), - = Microsoft = Jeff, ?

?

, SQL Server 2008.

UPDATE: , WorkItem.EmployeeId .

, Egil.

Answer 1

(CustomerId, EmployeeId)?

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

Answer 2

, , " ", .

Answer 3

, employeeId null int WorkItem? , , . , , employeeid workItem , , customerId, , .

, , , , , , .

Answer 4

?

• .., , ( ) .

• (, , ).

(1), , Employee. , MS , - , , CustomerId. WorkItem s. , , CustomerEmployee, . WorkItem .

(2) Employee CustomerId, EmployeeId. . Kieron.

Answer 5

, : (id_cia PK) product_group (id_cia FK , id_group PK) (id_group FK product_group, id_product PK, id_used_by_the_client null)

: id_used_by_the_client , . :

(1) =

(2) =

product_group (1, 1) = allowed

product_group (1,2) = allowed

product_group (2,3) = allowed

(1, 1, null) =

(1, 2, null) =

(1, 3, 1) =

(1, 4, 1) = , 1, 1, id_used_by_the_client = 1.

(2, 4, 1) = , 2, 1, id_used_by_the_client = 1.

(3, 4, 1) = , 3, 2, id_used_by_the_client = 1.

.

Answer 6

:

• EmployeeID (, , ) EmployeeID WorkItem , , Employee Customer WorkItem. WorkItem, Customer, Employee.

:

• WorkItem EmployeeID CustomerID Employee.

.