SQL表外键是复合主键的一部分 [英] SQL Table Foreign Key that is part of a Composite Primary Key

问题描述

是否有可能将一个表的外键作为另一个表的复合主键的一部分？
例如，如果我有两个表，一个包含所有不同用户的活动项目的信息，另一个包含项目正在使用的设备的信息：

项目表：
$ b $ p $复合主键：UserId，ProjectId （两者本身都是唯一的）

设备表：

复合主键：UserId，ProjectId，EquipmentId （既不是唯一的）

现在是否可以将设备表中的ProjectId设置为项目表中的外键？当我尝试时，我得到一个错误，说Project Table中的列不匹配现有的主键或唯一的约束？

解决方案

No。

创建外键时，在另一个表中指向的键必须是UNIQUE或PRIMARY KEY约束。您无法建立指向允许重复值的列的外键。如果你更新另一个表中的一个重复值（例如），那么很难想象数据应该如何起作用。

要做你所做的希望您必须建立ProjectID为UNIQUE或PRIMARY KEY的Projects表，然后将 中的外键指向该表的其他表。

<另外，您使用术语主键来描述组成主键的每个表中的列。实际上，每个表都只能有一个主键。该密钥可以由一列或多列组成，但密钥本身仍然被称为单数。这是使用主键优化搜索时的一个重要区别。

Is it possible to have a table's foreign key be part of another table's composite primary key? For example, if I have two tables, one contains information on all active projects of different users and another containing information on what equipment is being used by the projects:

Project Table:

Composite Primary Keys: UserId, ProjectId (neither are unique by themselves)

Equipment Table:

Composite Primary Keys: UserId, ProjectId, EquipmentId (neither are unique by themselves)

Now is it possible to set the ProjectId in the equipment table to be a foreign key from the project table? When I try, I get an error saying that the column in Project Table do not match an existing primary key or unique constraint?

解决方案

No.

When you create a foreign key, the key that you "point to" in the other table must be a UNIQUE or PRIMARY KEY constraint. You cannot establish a foreign key that points to a column that allow duplicate values. It would be very hard to imagine how the data should "act" if you update one of the duplicate values in the other table (for instance).

To do what you want you must establish a Projects table in which ProjectID is UNIQUE or a PRIMARY KEY and then point foreign keys in both the other tables to that table.

Parenthetically, you use the term "Primary Keys" to describe the columns in each table that make up the primary key. In fact, each table can have one and only one primary key. That key can be composed of one or more columns, but the key itself is still referred to the singular. This is an important difference when using the primary key to optimize searches.

这篇关于SQL表外键是复合主键的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！