SQL表外键是复合主键的一部分

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)

复合主键:UserId,ProjectId(两者都不是唯一的)

Equipment Table:

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

复合主键:UserId,ProjectId,EquipmentId(两者都不是唯一的)

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?

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

3 个解决方案

#1

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

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

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.

要执行您想要的操作,您必须建立一个Projects表,其中ProjectID是UNIQUE或PRIMARY KEY,然后将其他表中的外键指向该表。

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.

顺便说一句,您使用术语“主键”来描述构成主键的每个表中的列。实际上,每个表可以只有一个主键。该密钥可以由一个或多个列组成,但密钥本身仍然被称为单数。使用主键优化搜索时,这是一个重要的区别。

#2

It do not know if that's a good design practice but for sure it is possible to have a composite foreign key of one table that is the part of the composite primary key of other table.

它不知道这是一个好的设计实践,但可以肯定的是,有一个表的复合外键是其他表的复合主键的一部分。

Say we have a table test1 having a composite primary key (A, B)

假设我们有一个表test1具有复合主键(A,B)

Now we can have a table say test2 having primary key (P, Q, R) where in (P,Q) of test2 referencing (A,B) of test2.

现在我们可以有一个表说test2有主键(P,Q,R),其中test2的(P,Q)引用test2(A,B)。

I ran the following script in the MySql database and it works just fine.

我在MySql数据库中运行了以下脚本,它运行正常。

CREATE TABLE `test1` (
`A` INT NOT NULL,
`B` VARCHAR(2) NOT NULL,
`C` DATETIME NULL,
`D` VARCHAR(45) NULL,
PRIMARY KEY (`A`, `B`));

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NOT NULL,
`R` INT NOT NULL,
`S` DATETIME NULL,
`T` VARCHAR(8) NULL,
PRIMARY KEY (`P`, `Q`, `R`),
INDEX `PQ_idx` (`P`,`Q` ASC),
CONSTRAINT `PQ`
  FOREIGN KEY (`P`, `Q`)
  REFERENCES `test1` (`A`,`B`)
  ON DELETE CASCADE
  ON UPDATE CASCADE);

In the above mentioned case, the database is expecting the combination of (A,B) to be unique and it is, being a primary key in test1 table.

在上面提到的情况下,数据库期望(A,B)的组合是唯一的,并且它是test1表中的主键。

But if you try to do something like following, the script would fail. The database would not let you create the test2 table.

但是如果你尝试做类似跟随的事情,脚本就会失败。数据库不允许您创建test2表。

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NULL,
`R` DATETIME NULL,
`S` VARCHAR(8) NULL,
`T` VARCHAR(45) NULL,
  INDEX `P_idx` (`P` ASC),
  INDEX `Q_idx` (`Q` ASC),
  CONSTRAINT `P`
    FOREIGN KEY (`P`)
    REFERENCES `test1` (`A`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Q`
    FOREIGN KEY (`Q`)
    REFERENCES `test1` (`B`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

In the above mentioned case database would expect the column A to be unique individually and the same follows for column B. It does not matter if combination of (A,B) is unique.

在上面提到的情况下,数据库会期望列A是唯一的,对于列B也是如此。如果(A,B)的组合是唯一的,则无关紧要。

#3

-3

@Larry Lustig The foreign key can be part of primary key in the other table.

@Larry Lustig外键可以是另一个表中主键的一部分。

source: Dependent relationship

来源:依赖关系

Check relationship between tables: Zdarzenie(Event) and TypZdarzenia (type of event)

检查表之间的关系:Zdarzenie(事件)和TypZdarzenia(事件类型)

#1

No.