所以我对我的作业有这个问题。我有两个实体: Order,Gifr_cupon。我有两个表: Orders,Gift_Cupons。
每个订单可以有多个杯子,也可以没有。每个Cupon都绑定到no或单个订单。但每个订单只能激活一个can。
如何通过约束来实施这一点?
下面是DDL的逻辑视图和ER视图:
DLL:
CREATE TABLE gift_cupons (
cupon_id INTEGER NOT NULL,
order_order_id INTEGER,
active INTEGER NOT NULL
);
ALTER TABLE gift_cupons ADD CONSTRAINT gift_cupon_pk PRIMARY KEY ( cupon_id
);
ALTER TABLE gift_cupons ADD CHECK gift_cupon_check CHECK(active IS NULL OR ( active >= 0 AND active <=1 ) );
CREATE TABLE orders (
order_id INTEGER NOT NULL
);
ALTER TABLE orders ADD CONSTRAINT order_pk PRIMARY KEY ( order_id );
ALTER TABLE gift_cupons
ADD CONSTRAINT gift_cupon_order_fk FOREIGN KEY ( order_order_id )
REFERENCES orders ( order_id );
发布于 2019-06-14 03:12:50
有点
Cupon - is bound to -> Order;
Order - has active -> Cupon;
Cupon (
Id PK,
orderId FK Order.Id,
Unique ( Id, orderId) -- any superset of PK is unique
);
Order (
Id PK
ActiveCuponId,
(Id, ActiveCuponId) FK Cupon( OrderId, Id)
);
请参阅fiddle https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=596b30905d02a9e5c799b16da5fff5ab
发布于 2019-06-14 03:12:42
从gift_cupons
表中删除ACTIVE
列,并将此状态替换为orders
表中的外键,如下所示:
CREATE TABLE gift_cupons (
cupon_id INTEGER NOT NULL,
order_order_id INTEGER,
);
ALTER TABLE gift_cupons ADD CONSTRAINT gift_cupon_pk PRIMARY KEY ( cupon_id
);
CREATE TABLE orders (
order_id INTEGER NOT NULL
active_cupon INTEGER -- nullable
);
ALTER TABLE orders ADD CONSTRAINT order_pk PRIMARY KEY ( order_id );
ALTER TABLE gift_cupons
ADD CONSTRAINT gift_cupon_order_fk FOREIGN KEY ( order_order_id )
REFERENCES orders ( order_id );
alter table orders
add constraint order_active_cupon_fk foreign key (active_cupon)
references gift_cupons (cupon_id);
https://stackoverflow.com/questions/56586988
复制相似问题