给定带有部分索引的表定义:
BEGIN;
CREATE TABLE user_note (
user_id VARCHAR NOT NULL,
item_id VARCHAR,
note VARCHAR NOT NULL,
archived_at TIMESTAMP
);
CREATE UNIQUE INDEX active_note
ON user_note(user_id, item_id)
WHERE archived_at IS NULL;
END;
我想确保只有一个值(user_id, item_id)
尚未存档。此约束不应应用于具有archived_at
非空值的记录(即,我们应该允许给定配对有许多存档记录(user_id, item_id)
)。
user_id
当和都被指定时,上述约束将按预期工作item_id
:
BEGIN;
INSERT INTO user_note (user_id, item_id, note) VALUES ('user_1', 'item_1', 'A general note');
INSERT INTO user_note (user_id, item_id, note) VALUES ('user_1', 'item_1', 'A different note');
END;
给出以下错误:
BEGIN
INSERT 0 1
ERROR: duplicate key value violates unique constraint "active_note"
DETAIL: Key (user_id, item_id)=(user_1, item_1) already exists.
make: *** [populate] Error 1
但允许使用line_id
以下多个记录NULL
:
BEGIN;
INSERT INTO user_note (user_id, note) VALUES ('user_1', 'A general note');
INSERT INTO user_note (user_id, note) VALUES ('user_1', 'A different note');
END;
输出:
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
我还尝试过使用唯一索引,其空值不明显,如下所示:
BEGIN;
CREATE TABLE user_note (
user_id VARCHAR NOT NULL,
item_id VARCHAR,
note VARCHAR NOT NULL,
archived_at TIMESTAMP,
UNIQUE NULLS NOT DISTINCT (user_id, item_id)
);
END;
但这当然没有考虑到archived_at
价值:
BEGIN;
INSERT INTO user_note (user_id, note, archived_at) VALUES ('user_1', 'A general note', CURRENT_TIMESTAMP);
INSERT INTO user_note (user_id, note, archived_at) VALUES ('user_1', 'A different note', CURRENT_TIMESTAMP);
END;
我收到这个不需要的错误:
BEGIN
INSERT 0 1
ERROR: duplicate key value violates unique constraint "user_note_user_id_item_id_key"
DETAIL: Key (user_id, item_id)=(user_1, null) already exists.
make: *** [populate] Error 1
有没有办法在is(user_id, item_id)
时禁止多个条目,但在not时允许多个条目?archived_at
NULL
archived_at
NULL