各位大大好
目前我的user table為
user_name text, -- 使用者名稱
team_id text, -- 隊伍id(純欄位,並沒有team這個table)
position text, -- 職位('LEADER'/'MEMBER')
......及其他
我希望能設定當position = 'LEADER'時,(position, team_id)為唯一,因為一個隊伍只能有一個Leader。
我自己寫了
ADD CONSTRAINT position_check_unique CHECK (
position = 'MEMBER' OR (
position = 'LEADER' AND UNIQUE(position, team_id)
)
)
但顯示語法錯誤,查了一下後發現CHECK、UNIQUE沒看過連用的範例,DB的UI也並沒有可以一次設定兩者的地方,所以我這樣的處理方向應不正確
想問有沒有更好的解決方法?
請愛用 case. (甲骨文寫法, 但應該能在其他資料庫上使用)
drop table user_table;
CREATE TABLE user_table (
user_name varchar2(8) ,
team_id varchar2(8),
position varchar2(8));
insert into user_table values(1,'A','LEADER');
insert into user_table values(2,'A','MEMBER');
commit;
create unique index idx_ut on user_table(case when position='LEADER' then team_id else null end);
update user_table set position='LEADER' where user_name=2;
commit;
insert into user_table values(99,'A','LEADER');
commit;
select * from user_table;
CREATE TABLE user_table (
user_name text , --[使用者名稱]
team_id text, --[隊伍id]
position text); --[職位('LEADER'/'MEMBER')]
INSERT INTO user_table (user_name,team_id,position)
VALUES
(1,'A','LEADER'),
(2,'A','MEMBER'),
(3,'D','MEMBER'),
(4,'C','LEADER'),
(5,'X','MEMBER'),
(6,'A','MEMBER'),
(7,'D','LEADER'),
(8,'C','MEMBER'),
(9,'X','MEMBER'),
(10,'D',NULL),
(11,'C','MEMBER'),
(12,'C','MEMBER'),
(13,'D','');
INSERT 前先做 SELECT 判斷 COUNTER 是否=0
-- 隊伍= X ,職位=LEADER
SELECT COUNT(position) AS COUNTER
FROM user_table
WHERE position='LEADER' AND team_id='X';
UPDATE 指定隊伍變更 LEADER
-- 隊伍=A ,user_name='1' ,position= LEADER
-- 變更為隊伍=A ,user_name='6' ,position= LEADER
BEGIN;
UPDATE user_table
SET position='MEMBER'
WHERE team_id='A' AND NOT (position IS NULL OR position='');
UPDATE user_table
SET position='LEADER'
WHERE team_id='A' AND user_name ='6';
COMMIT;
請參閱 : How to create an index on CASE expression in Postgres
方法A:
CREATE UNIQUE INDEX test_index_2 ON user_table((CASE WHEN position='LEADER' THEN team_id END));
方法B:
CREATE UNIQUE INDEX test_index_2 ON user_table (team_id) WHERE position='LEADER' ;