1

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
5
  • Personally I think your DB design is wrong. You basically have a conditional join (either product or variant) you should have every product have at least one variant and join only on that Jan 31, 2021 at 4:15
  • @Charlieface the reason is that a price could be for the "base" product, thus without variant. Having a variant describing a base product (thus not actually varying the base product) is conceptually wrong imo
    – vabatta
    Feb 1, 2021 at 1:48
  • I suppose another way of describing it is that the price relates to a product, variant pair, for which variant could be null. But then you have no table which maps a product, variant relation with a null variant, only ones with extant variants. Because you can't have a nullable primary key, you would need a variant_id for such a row anyway. So it's not wrong to say that there are products having any number of variants, that number may be one. Feb 1, 2021 at 2:05
  • @Charlieface I am not sure to understand what you mean by "no table which maps a product, variant relation with a null variant, only ones with extant variants". What I am currently thinking of using is a table with the relation you described: price has a partial unique index for product, variant so that variant can be null and can be assigned once per product. A base product can have a price which is represented by the variant begin null. Using the price table, I can join product, variant and get a list of variants's product and viceversa.
    – vabatta
    Feb 5, 2021 at 2:48
  • Yes but you can't use it as a primary key, because by definition a primary key cannot be null Feb 5, 2021 at 9:20

2 Answers 2

1

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');
9
  • I tried the above approaches, but I felt that the variant_ext_id was a sort of hack and the "defaults" approach implies that I have a default empty variant 000000-.... which is not clean as well. Ended up by using an auto generated ID and an UNIQUE constraint on the two foreign keys.
    – vabatta
    Jan 30, 2021 at 12:21
  • You don't need the generated id, i'll add an explanation to my answer Jan 30, 2021 at 12:30
  • Other than that, I agree that all in my answer is "hacks". But, most of the time I would say that generated id's belong in that category as well. Jan 30, 2021 at 12:55
  • I don't get the purpose of the example table. In the last context, how would I insert the data for the table mentioned in the question?
    – vabatta
    Jan 30, 2021 at 12:58
  • The example table is just any child table of price. It demonstrates that there is no need for the generated id in price. Not sure what you mean with the table mentioned in the question, I thought that where price and my example already contains two inserts into that table Jan 30, 2021 at 13:02
1

After different attempts, as PRIMARY KEYs cannot have NULLs values of any sort, the most simple and effective approach was to generate an ID and use a UNIQUE constraint on the two foreign keys.

CREATE TABLE IF NOT EXISTS inventory.price (
  id                     UUID NOT NULL DEFAULT gen_random_uuid(),
  product_id             UUID NOT NULL,
  variant_id             UUID,
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (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_product_variant_unique UNIQUE (product_id, variant_id),
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.