创建PostgreSQL表和关系-带有关系的问题-一对一 [英] Creating PostgreSQL tables + relationships - PROBLEMS with relationships - ONE TO ONE
问题描述
因此，我应该完全按照ERD所描述的方式来创建此架构+关系.在这里，我仅显示遇到问题的表:
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:
因此，我试图使它一对一，但是由于某种原因，无论我进行什么更改，在具有外键的任何表上我都会得到一对多的选择.
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.
这是我用于这两个表的sql.
This is my sql for these two tables.
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.
这是第二张图片的代码.
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 )
);
我将不胜感激.谢谢.
推荐答案
一对一不能很好地表示为标准SQL中的一流关系类型.就像使用连接器表和两个一对多关系来实现多对多一样，SQL中没有真正的一对一".
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.
有两种选择:
-
创建普通的外键约束(一对多"样式)，然后在引用的FK列上添加
UNIQUE约束.这意味着在引用列中最多只能出现一个引用值，使其成为一对一的可选值.这是一种非常简单且相当宽容的方法，效果很好.
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.
使用可以建模为1:m的常规FK关系，并让您的应用确保实际上只有1:1的关系.我不建议这样做，添加FK唯一索引只有一个小的写性能缺点，它有助于确保数据有效性，查找应用程序错误，并避免使以后需要修改架构的其他人感到困惑.
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.
创建相互的外键-仅当数据库支持可延期的外键约束时才可以.这对代码来说有点复杂，但是允许您实现一对一的强制性关系.每个实体在唯一列中都有一个外键参考，指向其他人的PK.约束中的一个或两个必须为DEFERRABLE且为INITIALLY DEFERRED或与SET CONSTRAINTS调用一起使用，因为必须推迟其中一项约束检查才能建立循环依赖.这是一项相当先进的技术，对于绝大多数应用程序来说并不是必需的.
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 DEFERRABLE and either INITIALLY DEFERRED or used with a SET CONSTRAINTS call, 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.
如果数据库支持它们，请使用预提交触发器，因此您可以验证插入实体A时是否也确实插入了一个实体B，反之亦然，并进行了相应的更新和删除检查.这可能很慢并且通常是不必要的，而且许多数据库系统不支持预提交触发器.
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.
这篇关于创建PostgreSQL表和关系-带有关系的问题-一对一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
