数据库关系 1:1 vs 1:0..1
我正在努力了解这些关系。正如我下面所示，是否正确：
1) 通过简单地将父级的身份密钥也设为孩子？
2) 为了使其1:1，我在父表中添加一个 FK，连接两个 Identity 列？
这就是全部内容了吗？如果我的措辞不准确/不正确，请纠正我。
1:0..1
客户(Id {PK, Identity}, 姓名)
客户地址(CustomerId {PK, FK, Identity}, StreetName)
FK_CustomerAddress_Customer(CustomerId, Id )
1:1
客户(Id {PK, FK, Identity}, 姓名)
FK_Customer_CustomerAddress(Id, CustomerId)
客户地址(CustomerId {PK, FK, Identity }, Streetname)
FK_CustomerAddress_Costumer(CustomerId, Id)
EDIT1:
我相信以上都是错误的，解决方案如下：(?)
1:0..1
Customer （Id {PK、Identity}、姓名）
CustomerAddress（CustomerId {PK、FK、UNIQUE}、StreetName）
FK_CustomerAddress_Customer强>（客户ID，ID）
I'm trying to learn these relationships. Is it correct as I show below that:
1) I will have a 1:0..1 relation by simply making the identity key of the parent also the identity key of the child?
2) To make it 1:1, I add an FK in parent table, connecting the two Identity columns?
Is this all there is to it? And please correct me if my wording is not exact/correct.
1:0..1
Customer(Id {PK, Identity}, Name)
CustomerAddress(CustomerId {PK, FK, Identity}, StreetName)
FK_CustomerAddress_Customer(CustomerId, Id)
1:1
Customer(Id {PK, FK, Identity}, Name)
FK_Customer_CustomerAddress(Id, CustomerId)
CustomerAddress(CustomerId {PK, FK, Identity}, Streetname)
FK_CustomerAddress_Costumer(CustomerId, Id)
EDIT1:
I believe both above was wrong, and solution is like this: (?)
1:0..1
Customer(Id {PK, Identity}, Name)
CustomerAddress(CustomerId {PK, FK, UNIQUE}, StreetName)
FK_CustomerAddress_Customer(CustomerId, Id)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
两个表之间的 1:1 关系意味着每个表中的每个元组恰好映射到另一个表中的一个元组。这更正式地称为双射。这是 SQL 的一个限制，对于大多数实际目的，双射实际上不可能在 SQL 数据库中实现。这是因为 SQL 要求单独更新每个表，因此除非您暂时禁用其中一个约束，否则无法更新两个这样的表。
1:0/1 关系也称为投影（您的第一个示例）。满射是 SQL 样式“FOREIGN KEY”约束的标准行为，假设不允许使用空值。约束在关系的一侧始终是可选的。
注意：IDENTITY 属性与您的示例无关。定义关系的是键和外键约束。
A 1:1 relationship between two tables means that each tuple in each table maps to exactly one tuple in the other. This is more formally known as a Bijection. It's a limitation of SQL that for most practical purposes bijection is effectively impossible to achieve in a SQL database. That's because SQL requires each table to be updated individually and therefore there is no way to update two such tables unless you temporarily disable one of the constraints.
A 1:0/1 relationship is also known as an Surjection (your first example). Surjection is the standard behaviour of a SQL-style "FOREIGN KEY" constraint assuming nulls are not permitted. The constraint is always optional on one side of the relationship.
NOTE: The IDENTITY property isn't relevant to your example. It's the key and foreign key constraints that define the relationship.
外键只能指向一个方向；如果你让它指向两种方式，你永远无法插入一行，因为插入一次只能对一个表进行操作。
因此，您列出的 1:0,1 解决方案通常适用于这两种情况。据我所知，SQL Server 没有提供任何方法来保证 1:1 约束中的第二个 1。
A foreign key can only point one way; if you make it point two ways, you could never insert a row, since an insert can only operate on one table a time.
So the solution you list for 1:0,1 is typically used for both cases. As far as I know, SQL Server offers no way to enfore the second 1 in a 1:1 constraint.
关于该主题的有趣读物：
ON POFN* AND POOD* - Fabian 的两个互补数据库设计原则帕斯卡、休·达文和大卫·麦戈文
An interesting read on the subject:
ON POFN* AND POOD* - TWO COMPLEMENTARY DATABASE DESIGN PRINCIPLES with Fabian Pascal, Hugh Darwen and David McGoveran