PostgreSQL: NULL value in foreign key column
PostgresqlForeign KeysPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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