SQL 创建 PostgreSQL 表 + 关系 - 关系问题 - 一对一
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15037349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating PostgreSQL tables + relationships - PROBLEMS with relationships - ONE TO ONE
提问by Georgi Angelov
So I am supposed to create this schema + relationships exactly the way this ERD depicts it. Here I only show the tables that I am having problems with:
所以我应该完全按照这个 ERD 描述的方式创建这个模式 + 关系。在这里，我只显示我遇到问题的表：
So I am trying to make it one to one but for some reason, no matter what I change, I get one to many on whatever table has the foreign key.
所以我试图做到一对一，但出于某种原因，无论我改变什么，我都会在任何有外键的表上得到一对多。
This is my sql for these two tables.
这是我对这两个表的 sql。
CREATE TABLE lab4.factory(
factory_id INTEGER UNIQUE,
address VARCHAR(100) NOT NULL,
PRIMARY KEY ( factory_id )
);
CREATE TABLE lab4.employee(
employee_id INTEGER UNIQUE,
employee_name VARCHAR(100) NOT NULL,
factory_id INTEGER REFERENCES lab4.factory(factory_id),
PRIMARY KEY ( employee_id )
);
Here I get the same thing. I am not getting the one to one relationship but one to many. Invoiceline is a weak entity.
在这里，我得到了同样的东西。我不是一对一的关系，而是一对多的关系。Invoiceline 是一个弱实体。
And here is my code for the second image.
这是我的第二张图片的代码。
CREATE TABLE lab4.product(
product_id INTEGER PRIMARY KEY,
product_name INTEGER NOT NULL
);
CREATE TABLE lab4.invoiceLine(
line_number INTEGER NOT NULL,
quantity INTEGER NOT NULL,
curr_price INTEGER NOT NULL,
inv_no INTEGER REFERENCES invoice,
product_id INTEGER REFERENCES lab4.product(product_id),
PRIMARY KEY ( inv_no, line_number )
);
I would appreciate any help. Thanks.
我将不胜感激任何帮助。谢谢。
回答by Craig Ringer
One-to-one isn't well represented as a first-class relationship type in standard SQL. Much like many-to-many, which is achieved using a connector table and two one-to-many relationships, there's no true "one to one" in SQL.
一对一在标准 SQL 中没有很好地表示为一流的关系类型。就像使用连接器表和两个一对多关系实现的多对多一样，SQL 中没有真正的“一对一”。
There are a couple of options:
有几个选项：
Create an ordinary foreign key constraint ("one to many" style) and then add a
UNIQUEconstraint on the referring FK column. This means that no more than one of the referred-to values may appear in the referring column, making it one-to-one optional. This is a fairly simple and quite forgiving approach that works well.Use a normal FK relationship that could model 1:m, and let your app ensure it's only ever 1:1 in practice. I do not recommend this, there's only a small write performance downside to adding the FK unique index and it helps ensure data validity, find app bugs, and avoid confusing someone else who needs to modify the schema later.
Create reciprocal foreign keys - possible only if your database supports deferrable foreign key constraints. This is a bit more complex to code, but allows you to implement one-to-one mandatory relationships. Each entity has a foreign key reference to the others' PK in a unique column. One or both of the constraints must be
DEFERRABLEand eitherINITIALLY DEFERREDor used with aSET CONSTRAINTScall, since you must defer one of the constraint checks to set up the circular dependency. This is a fairly advanced technique that is not necessary for the vast majority of applications.Use pre-commit triggers if your database supports them, so you can verify that when entity A is inserted exactly one entity B is also inserted and vice versa, with corresponding checks for updates and deletes. This can be slow and is usually unnecessary, plus many database systems don't support pre-commit triggers.
创建一个普通的外键约束（“一对多”样式），然后
UNIQUE在引用 FK 列上添加一个约束。这意味着在引用列中只能出现一个引用值，使其成为一对一的可选值。这是一种相当简单且相当宽容的方法，效果很好。使用可以模拟 1:m 的正常 FK 关系，并让您的应用程序确保它在实践中始终只有 1:1。我不建议这样做，添加 FK 唯一索引只会带来很小的写入性能下降，它有助于确保数据有效性、查找应用程序错误并避免混淆其他需要稍后修改架构的人。
创建互惠外键 - 仅当您的数据库支持可延迟外键约束时才可能。这对代码来说有点复杂，但允许您实现一对一的强制关系。每个实体在唯一的列中都有一个指向其他实体的 PK 的外键引用。一个或两个约束条件必须是
DEFERRABLE，要么INITIALLY DEFERRED或有使用SET CONSTRAINTS电话，因为你必须推迟约束检查建立循环依赖的一个。这是一种相当先进的技术，对于绝大多数应用程序来说并不是必需的。如果您的数据库支持，请使用预提交触发器，这样您就可以验证在插入实体 A 时是否也插入了一个实体 B，反之亦然，并进行相应的更新和删除检查。这可能很慢并且通常是不必要的，而且许多数据库系统不支持预提交触发器。
