共享的 SQL 规则/约束

Question

我有一个包含列的表格:

device_id
account_id
is_owner

以 device_id 和帐户 id 作为联合主键

我如何创建一个 SQL 规则/约束，以便当 is_owner 与联合主键为真时，它将不允许其他组合

device_id = (specific device id)
account_id = (any account id) 
is_owner = true

由于设备的所有者应该只有一个，

还有:

device_id  = (specific device id)
account_id = (any account id)   
is_owner = false

可以是“很多”，因为所有者帐户可以将设备共享给另一个帐户？

Answer 1

你可以使用部分索引:

CREATE UNIQUE INDEX idx_name 
   ON table_name(device_id, account_id)       --or only (device_id) if needed
WHERE is_owner = true;

DBFiddle Demo

例子:

CREATE TABLE t(device_id INT NOT NULL, account_id INT NOT NULL, is_owner BOOL);
CREATE UNIQUE INDEX idx_name ON t(device_id, account_id)
WHERE is_owner = true;
INSERT INTO t(device_id, account_id, is_owner)
VALUES( 1,1, false),(1,1,false);

SELECT * FROM t;

INSERT INTO t(device_id, account_id, is_owner)
VALUES( 1,2, true),(1,2,true);
--ERROR:  duplicate key value violates unique constraint "idx_name"
--DETAIL:  Key (device_id, account_id)=(1, 2) already exists.