如何使我的购物车表在postgress中正常工作

Question

我们试图输入的是字段，我们选择在atept中手动插入cart_id，以便对多个项目(如商店(使用相同的cart:

BEGIN;
INSERT INTO cart_tb(cart_id, inventory_id, desired_amount, row_status)
VALUES
(1, 1, 2, 1),
(2, 3, 1, 1),
(2, 1, 1, 1);
END;

基本上它的工作原理是，我只能有一个cart_id，但可以有多个库存id(inv_id(，但inv_id和cart_id的组合应该是唯一的，所以我可以有不同的cart，它们可以像在现实世界中一样有相同的物品。这是我当前的表格：

CREATE TABLE public.cart_tb
(
cart_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
inventory_id integer NOT NULL,
desired_amount integer NOT NULL,
row_status integer NOT NULL,
CONSTRAINT "cart_PK" PRIMARY KEY (cart_id),
CONSTRAINT "cart_UK" UNIQUE (cart_id, inventory_id),
CONSTRAINT "cart_inventory_FK" FOREIGN KEY (inventory_id)
REFERENCES public.inventory_tb (inventory_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE public.cart_tb
OWNER to postgres;

关于如何解决这个问题，有什么建议吗？我一直有一个关键的冲突。。

ERROR:  duplicate key value violates unique constraint "cart_PK"
DETAIL:  Key (cart_id)=(2) already exists.
SQL state: 23505

Answer 1

ok似乎需要一个复合主键，但我建议添加一个id并将其作为主键，并在cart_id、inventory_id上添加一个唯一键：

CREATE TABLE public.cart_tb
(
cart_id_pk integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
cart_id integer not null,
inventory_id integer NOT NULL,
desired_amount integer NOT NULL,
row_status integer NOT NULL,
CONSTRAINT "cart_PK" PRIMARY KEY (cart_id_pk),
CONSTRAINT "cart_UK" UNIQUE (cart_id, inventory_id),
CONSTRAINT "cart_inventory_FK" FOREIGN KEY (inventory_id)
REFERENCES public.inventory_tb (inventory_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

如果没有，您可以将cart_id和inventory_id都设置为复合主键：

CONSTRAINT "cart_PK" PRIMARY KEY (cart_id, inventory_id)

在这种情况下，您不再需要唯一的密钥。