每个关系约束只有一个活动 [英] Only one active for each relationship constraint

问题描述

所以我的作业遇到了这个问题. 我有两个实体:Order，Gifr_cupon. 我有两个表:Orders，Gift_Cupons.

So i have this problem with my assignment. I have two entities: Order, Gifr_cupon. I have two tables: Orders, Gift_Cupons.

每个订单可以有很多勋章，也可以没有.每个Cupon均无订单或单订单.但是每个订单只能激活一个cupon.

Each order can have many cupons or none. Each Cupon is bound to no or an single order. But only one cupon can be active for each order.

如何通过约束来强制执行此操作?

How to enforce this by constraint?

在这里使用DDL进行逻辑视图和ER视图:

Heres a logical and ER view with DDL:

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

推荐答案

种类

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

请参见小提琴 https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=596b30ffc5a16799

这篇关于每个关系约束只有一个活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！