在 SQL Server 中设计 1:1 和 1:m 关系

Designing 1:1 and 1:m relationships in SQL Server

提问by blade3

In SQL Server 2008, how does one design a 1:1 and 1:m relationship?

在 SQL Server 2008 中，如何设计 1:1 和 1:m 的关系？

回答by Charles Bretana

Any relationship requires that the "parent" table (the one side) have a Primary (or unique) Key (PK), that uniquely identifies each row, and the "child" table (the other side) have a Foreign Key column or columns, that must be populated with values that are the same as some existing value[s] of the Primary Key in the parent table. If you want a one to many (1-M) relationship then the Foreign Key should be an ordinary attribute (column or columns) in the child table that can repeat (there can be many rows with the same value)

任何关系都要求“父”表（一侧）有一个主（或唯一）键（PK），唯一标识每一行，“子”表（另一侧）有一个或多个外键列，必须填充与父表中主键的某些现有值相同的值。如果您想要一对多 (1-M) 关系，那么外键应该是子表中可以重复的普通属性（一列或多列）（可以有许多行具有相同的值）

If you want a one to one (1-1) relationship then the Foreign key should itself be a Primary Key or unique index in the child table that guarantees that there may be at most one row in the child table with that value.

如果您想要一对一 (1-1) 的关系，那么外键本身应该是子表中的主键或唯一索引，以保证子表中最多可能有一行具有该值。

A 1-1 relationship effectively partitions the attributes (columns) in a table into two tables. This is called vertical segmentation. This is often done for sub-classingthe table entities, or, for another reason, if the usage patterns on the columns in the table indicate that a few of the columns need to be accessed significantly more often than the rest of the columns. (Say one or two columns will be accessed 1000s of times per second and the other 40 columns will be accessed only once a month). Partitioning the table in this way in effect will optimize the storage pattern for those two different queries.

1-1 关系有效地将表中的属性（列）划分为两个表。这称为垂直分割。这通常用于对表实体进行子分类，或者出于另一个原因，如果表中列的使用模式表明其中一些列需要比其余列更频繁地访问。（假设一两列每秒将被访问 1000 次，而其他 40 列将每月仅被访问一次）。以这种方式对表进行分区实际上将优化这两个不同查询的存储模式。

Sub-Classing. The above actually creates a 1 to zero or one relationship, which is used for what is called a sub-class or subtype relationship. This occurs when you have two different entities that share a great number of attributes, but one of the entities has additional attributes that the other does not need. A good example might be Employees, and SalariedEmployees. The Employeetable would have all the attributes that all employees share, and the SalariedEmployeetable would exist in a (1-0/1) relationship with Employees, with the additional attributes (Salary, AnnualVacation, etc.) that only Salaried employees need.

子分类。上面实际上创建了一个 1 对零或一个的关系，用于所谓的子类或子类型关系。当您有两个共享大量属性的不同实体，但其中一个实体具有另一个不需要的附加属性时，就会发生这种情况。一个很好的例子可能是Employees和SalariedEmployees。该员工表将拥有所有所有员工共享，和属性SalariedEmployee表将存在于与企业员工（1-0 / 1）的关系，与附加属性（工资，AnnualVacation等），只有受薪雇员需要。

If you really want a 1-1 relationship, then you have to add another mechanism to guarantee that the child table will always have one record for each record/row in the parent table. Generally the only way to do this is by enforcing this in the code used to insert data (either in a trigger, stored procedure or code outside the database). This is because if you added referential integrity constraints on two tables that require that rows always be in both, it would not be possible to add a row to either one without violating one of the constraints, and you can't add a row to both tables at the same time.

如果你真的想要一个 1-1 的关系，那么你必须添加另一种机制来保证子表对于父表中的每个记录/行总是有一个记录。通常，唯一的方法是在用于插入数据的代码中强制执行此操作（在触发器、存储过程或数据库外的代码中）。这是因为如果您在要求行始终在两个表中的两个表上添加参照完整性约束，则不可能在不违反其中一个约束的情况下向任一表添加行，并且您无法向两个表添加行同时表。

回答by Thomas

One-to-One Relationship

一对一关系

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null [Primary Key, Unique]
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( ForeignKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this case, I can never have more than one row in the ChildTable for a given ParentTable primary key value. Note that even in a One-to-One relationship, one of the tables is the "parent" table. What differentiates a One-to-One relationship from a One-to-Many relationship purely in terms of implementation is whether the ChildTable's foreign key value has a Unique or Primary Key constraint.

在这种情况下，对于给定的 ParentTable 主键值，我在 ChildTable 中永远不能有超过一行。请注意，即使在一对一关系中，其中一张表也是“父”表。纯粹在实现方面，一对一关系与一对多关系的区别在于 ChildTable 的外键值是否具有唯一或主键约束。

One-to-Many Relationship

一对多关系

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null 
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( PrimaryKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this scenario, I can have multiple rows in the ChildTable for a given ParentTable primary key value.

在这种情况下，对于给定的 ParentTable 主键值，我可以在 ChildTable 中有多行。

回答by rayman86

A 1:1 relationship exists where table A and table B only exist once in regards to each other. Example: A student has 1 master student record. The student would be table A and the record in table B. Table B would contain a foreign key to the student record in table A (and possibly vice-versa)

存在 1:1 关系，其中表 A 和表 B 彼此仅存在一次。示例：学生有 1 个硕士生记录。学生将是表 A 和表 B 中的记录。表 B 将包含表 A 中学生记录的外键（反之亦然）

A 1:m relationship exists where table A can be referenced or linked to by many entries in table B. Example: A student can take several books out from the library. The student again would be table A and the book could be the entry in table B. The entry in table B would contain a foreign key to who checked the book out, and many books could reference the same student.

存在 1:m 关系，其中表 A 可以被表 B 中的许多条目引用或链接。示例：学生可以从图书馆中取出几本书。学生将再次成为表 A，而这本书可能是表 B 中的条目。表 B 中的条目将包含一个外键，指示谁签出了这本书，并且许多书籍可以引用同一个学生。