PostgreSQL：外键列中的 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23325838/
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
PostgreSQL: NULL value in foreign key column
提问by Neppomuk
In my PostgreSQL database I have the following tables (simplified):
在我的 PostgreSQL 数据库中，我有以下表格（简化版）：
CREATE TABLE quotations ( receipt_id bigint NOT NULL PRIMARY KEY ); CREATE TABLE order_confirmations ( receipt_id bigint NOT NULL PRIMARY KEY fk_quotation_receipt_id bigint REFERENCES quotations (receipt_id) );
My problem now reads as follows:
我的问题现在如下：
I have orders which relate to previous quotations (which is fine 'cause I can attach such an order to the quotation referenced by using the FK field), but I also have placed-from-scratch orders withouta matching quotation. The FK field would then be NULL, if the database let me, of course. Unfortunately, I get an errorwhen trying to set fk_quotation_receipt_id to NULL in an INSERT statement because of a violatedforeign key constraint.
我有与先前报价相关的订单（这很好，因为我可以将这样的订单附加到使用 FK 字段引用的报价中），但我也有没有匹配报价的从头开始下的订单。当然，如果数据库允许的话，FK 字段将为 NULL。不幸的是，由于违反了外键约束，我在 INSERT 语句中尝试将 fk_quotation_receipt_id 设置为 NULL 时出错。
When designing these tables I was still using PgSQL 8.2, which allowed NULL values. Now I've got 9.1.6, which does not allow for this.
在设计这些表时，我仍然使用 PgSQL 8.2，它允许 NULL 值。现在我有 9.1.6，它不允许这样做。
What I wish is an optional (or nullable)foreign key constraint order_confirmations (fk_quotation_receipt_id) → quotations (receipt_id). I can't find any hints in the official PgSQL docs, and similar issues posted by other users are already quite old.
我希望的是一个可选的（或可为空的）外键约束 order_confirmations (fk_quotation_receipt_id) → 引用 (receipt_id)。我在官方 PgSQL 文档中找不到任何提示，其他用户发布的类似问题已经很老了。
Thank you for any useful hints.
感谢您提供任何有用的提示。
采纳答案by Clodoaldo Neto
Works for me in 9.3 after correcting a missing comma. I'm sure it will work also in 9.1
更正丢失的逗号后，在 9.3 中对我有用。我相信它也适用于 9.1
create table quotations (
receipt_id bigint not null primary key
);
create table order_confirmations (
receipt_id bigint not null primary key,
fk_quotation_receipt_id bigint references quotations (receipt_id)
);
insert into order_confirmations (receipt_id, fk_quotation_receipt_id) values
(1, null);
Confirmationwill include:
确认将包括：
INSERT 0 1
