当插入引用其他两个表的第三个表时，如何检查两个表之间的约束？

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 * (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?

例如,如果Scott(员工)在Microsoft(客户)工作,Jeff(员工)在*(客户)工作,我如何阻止某人将工作项添加到数据库中,customer = Microsoft,employee = Jeff,哪个没有意义?

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.

应该提一下我使用的是SQL Server 2008。

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

更新:我应该补充一点,WorkItem.EmployeeId可以为null。

Thanks, Egil.

6 个解决方案

#1

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

复合列(CustomerId,EmployeeId)上的外键是否有效?

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

#2

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.

您可以通过创建跨越这些表的视图“WITH SCHEMABINDING”并强制执行各个表的集合约束来实现此目的。

#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.

为什么您希望employeeId为null int WorkItem?也许你应该添加另一个表来避免这种特殊的怪异。从我能看到的最简单的事情是在workItem中为employeeid添加一个唯一约束,如果这是你想要的,甚至可能在customerId上是唯一的。

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.

添加跨越多个表的约束的更一般方法是定义应始终为空的视图,并添加它为空的约束。

#4

What are you trying to model here?

你想在这里建模什么?

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.

您是一个承包代理商或类似机构,并且您有一堆承包商(在一段时间内)分配给客户。
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.

在情况(1)中,看起来您的Employee表有问题。特别是,当Scott与MS签订合同并且与其他人签订合同时,您无法保留历史数据,因为您需要更改CustomerId。这也使所有WorkItem无效。相反,您应该有第四个表,例如CustomerEmployee来存储它。然后WorkItem应该引用该表。

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.

在情况(2)中,您在Employee上的主键应该是CustomerId,EmployeeId。两个客户可以拥有相同的员工ID号。然后Kieron的外键将起作用。

#5

I recently pass to a similar situation, consider the schema: Table company (id_cia PK) Table product_group (id_cia FK to company, id_group PK) Table products (id_group FK to product_group, id_product PK, id_used_by_the_client null)

我最近传递到类似的情况,考虑架构:表公司(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)

Rule: The database must allow only one id_used_by_the_client for each product of a company but this filed can be null. Example:

规则:数据库必须只允许公司的每个产品使用一个id_used_by_the_client,但此字段可以为null。例:

Insert into company (1) = allowed

插入公司(1)=允许

Insert into company (2) = allowed

插入公司(2)=允许

Insert into product_group (1, 1) = allowed

插入到product_group(1,1)=允许

Insert into product_group (1,2) = allowed

插入product_group(1,2)=允许

Insert into product_group (2,3) = allowed

插入product_group(2,3)=允许

Insert into products values (1, 1, null) = allowed

插入产品值(1,1,null)=允许

Insert into products values (1, 2, null) = allowed

插入产品值(1,2,null)=允许

Insert into products values (1, 3, 1) = allowed

插入产品值(1,3,1)=允许

Insert into products values (1, 4, 1) = not allowed, in the group 1 that belongs to company 1 already exists an id_used_by_the_client = 1.

插入产品值(1,4,1)=不允许,在属于公司1的组1中已存在id_used_by_the_client = 1。

Insert into products values (2, 4, 1) = not allowed, in the group 2 that belongs to company 1 already exists an id_used_by_the_client = 1.

插入产品值(2,4,1)=不允许,在属于公司1的组2中已存在id_used_by_the_client = 1。

Insert into products values (3, 4, 1) = allowed, in the group 3 that belongs to company 2 there is no id_used_by_the_client = 1.

插入产品值(3,4,1)=允许,在属于公司2的组3中,没有id_used_by_the_client = 1。

I decided to use a trigger to control this integrity.

我决定使用触发器来控制这种完整性。

#6

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.

将EmployeeID列设置为Employee的主键(可能还有auto-id),并将WorkeeID记录中的EmployeeID存储为外键,而不是将Workee和Customer ID存储在WorkItem中。您可以通过Employee表连接到Customer表来检索WorkItem的Customer详细信息。

Or:

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

使WorkItem的EmployeeID和CustomerID列成为Employee的复合外键。

I favour the first approach, personally.

我个人赞成第一种方法。

#1