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.

Bougie

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

Selfsupport

The missing comma was not the issue (I simply forgot it when copying, grrrrr). It turned out that the client code added a 0 instead of a NULL, and as this was a prepared statement, I did not see it in the logs. Nevertheless thanx a lot for your answer. – Bougie

This was super helpful - I was looking around everywhere why null values are not accepted, all the while I was just trying to update the field to "" instead of null. – Flawy

Answer 2

I keep trying to remember this case every now and then. So following atomic case helps me to remember:

drop table if exists t2;
drop table if exists t1;
create table t1 (
    id integer generated by default as identity primary key
);

create table t2 (
    id integer generated by default as identity primary key,
    t1_id int references t1(id)
);

insert into t1 values (1);

insert into t2 values (1, 1);    -- OK, 1 exists so accepted
insert into t2 values (2, null); -- OK, null is accepted
insert into t2 values (3, 2);    -- NOK, 2 is not accepted

If you want to forbid the null case, then it is not related with foreign key but with the declaration of the column (observe the new coming not null part):

...
create table t2 (
    id integer generated by default as identity primary key,
    t1_id int not null references t1(id)
);
...

PS: Capital letter reserved words reduce SQL's readability. Please use small letters.

Lottie