在SQL Server中设计1：1和1：m关系 [英] Designing 1:1 and 1:m relationships in SQL Server

问题描述

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

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

推荐答案

任何关系都需要父表（一侧）具有唯一标识每行的主（或唯一）键（PK），子表（另一侧）具有外键列或列填充与父表中主键的某些现有值[s]相同的值。如果你想要一对多（1-M）关系，那么外键应该是一个普通的属性（列或列）在子表中可以重复（可以有很多行具有相同的值）

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)

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

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关系有效地将表中的属性（列）分成两个表。这被称为垂直分割。这通常是为表实体进行子类化，或者由于另一个原因，如果表中的列的使用模式表明需要访问的列数量要明显多于剩下的列。 （说一个或两个列将被访问每秒1000次，其他40列只能访问一个月一次）。以这种方式实际分区表将优化这两种不同查询的存储模式。

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-classing the 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到零或一个关系，它被用于所谓的子类或子类型关系。当您有两个共享大量属性的不同实体时，会发生这种情况，但其中一个实体具有另一个不需要的附加属性。一个很好的例子可能是雇员和 SalariedEmployees 。 Employee 表将具有所有员工共享的所有属性，而SalariedEmployee表将以与Employees（1-0 / 1）的关系存在，附加属性（工资， AnnualVacation 等），只有Salaried员工需要。

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 Employee table would have all the attributes that all employees share, and the SalariedEmployee table would exist in a (1-0/1) relationship with Employees, with the additional attributes (Salary, AnnualVacation, etc.) that only Salaried employees need.

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

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.

这篇关于在SQL Server中设计1：1和1：m关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！