iT邦幫忙

0

【請益】PostgresQL 想設定多欄位於特定情況下唯一

各位大大好

目前我的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也並沒有可以一次設定兩者的地方,所以我這樣的處理方向應不正確

想問有沒有更好的解決方法?

2 個回答

1
pojen
iT邦新手 1 級 ‧ 2020-12-20 23:44:32
最佳解答

請愛用 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;

https://ithelp.ithome.com.tw/upload/images/20201220/200333534X7dTJy9ih.png

感謝大神的幫忙~~~
目前還在試驗中,看有沒有什例外狀況,至今是沒有啦~~

使用index的做法遠超出我的想像,我一直以來都以為,index是為了增加索引效率,用硬體空間換取時間,用寫入成本換取讀取速度的效率調節工具,在遇到效率問題前不要亂碰,畢竟可能越調越糟。

沒想到能有這種用法~~開了眼界

1
rogeryao
iT邦大師 3 級 ‧ 2020-12-18 19:19:35
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;

Demo

請參閱 : 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' ;

有DEMO實在是太有心了~~
後續又補充了index的相關文件
真的非常感謝大神的幫忙~~~

我要發表回答

立即登入回答