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

发布于 2024-10-19 06:38:00 字数 39 浏览 3 评论 0原文

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌化 2024-10-26 06:38:00

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

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

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

子分类。上面实际上创建了一个 1 到 0 或 1 的关系，用于所谓的子类或子类型关系。当您有两个不同的实体共享大量属性，但其中一个实体具有另一个实体不需要的附加属性时，就会发生这种情况。一个很好的例子可能是Employees和SalariedEmployees。 Employee 表将包含所有员工共享的所有属性，SalariedEmployee 表将与员工以 (1-0/1) 关系存在，并具有附加属性（薪水、年假等）只有受薪员工才需要。

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

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)

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.

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.

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.

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.

回复收藏 0 原文

对你的占有欲 2024-10-26 06:38:00

一对一关系

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

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

一对多关系

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

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

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

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.

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.

回复收藏 0 原文