如何在復合主鍵的外鍵中允許 NULL

我想NULL在復合主鍵的外鍵中有 s 。這是我期望的有效數據的範例。

我無法弄清楚為什麼 postgres 中的以下 SQL 在插入asNOT NULL時會給我帶來違規約束。NULL``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)
);

並且檢查以information_schema確認不可為空的約束。

我相信主鍵約束強制執行非空約束。可以看下面的Fiddle

我不確定你想要實現什麼。以下內容是否有效：

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

? 如果不是（即每個產品只允許一個空變數），您可以使用生成的列並在那裡添加約束：

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)
);

當然，您需要在任何依賴表中應用該規則。從規範化的角度來看，您可能希望以不同的方式對待產品和產品的變體。

想到的其他事情是使用預設值，例如：

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)
);

編輯：另一種選擇是將主鍵更改為唯一約束，請參閱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');

引用自：https://dba.stackexchange.com/questions/284321