Postgresql – How to allow NULL in foreign keys of a compound primary key

foreign keynullpostgresqlprimary-keyschema

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id variant_id
123-123 ABC
123-123 NULL
456-456 ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.

CREATE TABLE IF NOT EXISTS inventory.price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES inventory.product (id) MATCH FULL,
  CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)
    REFERENCES inventory.variant (id) MATCH SIMPLE,
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

And the inspection to information_schema confirms the non-nullable constraint.

column_name column_default is_nullable data_type
product_id NULL NO uuid
variant_id NULL NO uuid
amount NULL NO money
created_at now() NO timestamp with time zone

Best Answer

I believe that the primary key constraint enforces the not null constraint. You can look at the following Fiddle

I'm not sure what it is that you want to achieve. Should the following be valid:

INSERT INTO price (product_id, variant_id, ...
VALUES ('123-123', null, ...)
     , ('123-123', null, ...)

? If not (i.e. only one allowed null varant per product), you can use a generated column and add the constraint there:

CREATE TABLE IF NOT EXISTS price3 (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  variant_ext_id         UUID NOT NULL GENERATED ALWAYS AS (COALESCE(variant_id, product_id)) STORED,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey3 PRIMARY KEY (product_id, variant_ext_id),
  CONSTRAINT inventory_price_amount_gt_03 CHECK (amount > '0'::money)
);

You would, of course, need to apply that rule in any dependent tables. From a normalisation point of view, you may want to treat products and variant of products differently.

Other things that come to mind is to use a default value such as:

CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID DEFAULT '00000000-0000-0000-0000-000000000000' NOT NULL,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

Edit: Another option is to change the primary key to a unique constraint, see Fiddle:

CREATE TABLE IF NOT EXISTS product (
  product_id UUID NOT NULL PRIMARY KEY
);

insert into product values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

CREATE TABLE IF NOT EXISTS price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey UNIQUE (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES product (product_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

insert into price (product_id, amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53','12');

insert into price (product_id, variant_id ,amount)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54', '19');


CREATE TABLE example ( 
  product_id             UUID NOT NULL,
  variant_id             UUID,
  foreign key (product_id) references product (product_id),
  foreign key (product_id, variant_id) references price (product_id, variant_id)
);


-- valid, f.k. evaluates to Null
insert into example (product_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53');

-- valid, f.k. evaluates to True
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e54');

-- invalid, f.k. evaluates to False
insert into example (product_id, variant_id)
values ('33a9fb48-5c0c-4bec-a1d9-382a73856e53', '33a9fb48-5c0c-4bec-a1d9-382a73856e55');
Related Question