SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE gift_cupons (
	id         INTEGER NOT NULL PRIMARY KEY,
	order_id   INTEGER NULL,
	UNIQUE (id, order_id)
);


CREATE TABLE orders (
	id INTEGER NOT NULL PRIMARY KEY,
	activeCupon_id INTEGER NULL,
    FOREIGN KEY (activeCupon_id, id) REFERENCES gift_cupons (id, order_id )
);

INSERT gift_cupons (id)
VALUES (1),(2),(3);

INSERT orders (id)
VALUES (10),(20),(30);
6 rows affected

-- OK
UPDATE gift_cupons
SET order_id = 10
WHERE id <3;

UPDATE orders
SET activeCupon_id = 1
WHERE id = 10;

SELECT *  FROM gift_cupons;
SELECT *  FROM orders;


id
order_id


1
10

2
10

3
null




id
activeCupon_id


10
1

20
null

30
null


-- fails due to FK - cann't set the cupon active when it's not assigned.
UPDATE orders
SET activeCupon_id = 1
WHERE id = 20;
Msg 547 Level 16 State 0 Line 2
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__orders__398D8EEE". The conflict occurred in database "fiddle_8fcb905e85fd4376a2e6e2c3ac6526ae", table "dbo.gift_cupons".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

-- fails due to FK - cann't assign the cupon to the order when it's active in the other order.
UPDATE gift_cupons
SET order_id = 20
WHERE id = 1;
Msg 547 Level 16 State 0 Line 2
The UPDATE statement conflicted with the REFERENCE constraint "FK__orders__398D8EEE". The conflict occurred in database "fiddle_8fcb905e85fd4376a2e6e2c3ac6526ae", table "dbo.orders".
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
id	order_id
1	10
2	10
3	null
id	activeCupon_id
10	1
20	null
30	null