在 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)，唯一标识每一行，并且子"表(另一侧)有一个或多个外键列，必须填充与父表中主键的某些现有值相同的值.如果你想要一对多(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 到 0 或一个的关系，用于所谓的子类或子类型关系.当您拥有共享大量属性的两个不同实体，但其中一个实体具有另一个不需要的附加属性时，就会发生这种情况.Employees 和 SalariedEmployees 就是一个很好的例子.Employee 表将拥有所有员工共享的所有属性，而 SalariedEmployee 表将与员工存在 (1-0/1) 关系，并带有附加属性(Salary、AnnualVacation 等)只有受薪员工才需要.

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屋！