How to allow NULL in foreign keys of a compound primary key 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 &gt; '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
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 &gt; '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 &gt; '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 &gt; '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');