I want to have NULL
s 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 |