PostgreSQL: NULL value in foreign key column

Question

In my PostgreSQL database I have the following tables (simplified):

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 without a matching quotation. The FK field would then be NULL, if the database let me, of course. Unfortunately, I get an error when trying to set fk_quotation_receipt_id to NULL in an INSERT statement because of a violated foreign key constraint.

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.

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.

Thank you for any useful hints.

Answer 1

Works for me in 9.3 after correcting a missing comma. I'm sure it will work also in 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); 

Confirmation will include:

INSERT 0 1