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

#postgresql sql postgresql

finis199466 2020-12-18 17:52:35 ‧ 1520 瀏覽

分享至

各位大大好

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

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

pojen

iT邦研究生 5 級 ‧ 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;

回應 1
分享
檢舉

finis199466 iT邦新手 3 級 ‧ 2020-12-22 14:46:36 檢舉

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

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

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

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 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' ;