Oracle：允许空值时为用户ID实现电话号码唯一约束

要实现“每个User ID仅允许一条非空电话号码记录，允许多条NULL记录”的约束，Oracle中可以通过函数型唯一索引来实现，利用Oracle对唯一索引中NULL值的处理规则（多个NULL不被视为重复）。

具体实现 #

假设你的表名为user_contacts，包含user_id（用户ID）和phone（电话号码）列，执行以下SQL创建索引：

CREATE UNIQUE INDEX idx_user_single_non_null_phone ON user_contacts (
    user_id,
    CASE WHEN phone IS NOT NULL THEN 1 END
);

原理说明 #

• 当phone列非空时，CASE表达式返回固定值1，此时索引的键为(user_id, 1)。如果同一user_id下存在多条非空phone记录，它们的索引键完全相同，会触发唯一索引的唯一性约束，阻止插入/更新操作。
• 当phone列为NULL时，CASE表达式返回NULL，索引的键为(user_id, NULL)。Oracle的唯一索引允许多个(user_id, NULL)条目（因为NULL不与任何值相等，包括其他NULL），所以多条NULL记录可以正常保存。

测试验证 #

1. 插入合法记录（同一user_id下多条NULL）：

INSERT INTO user_contacts (user_id, phone) VALUES (1, NULL);
INSERT INTO user_contacts (user_id, phone) VALUES (1, NULL);
INSERT INTO user_contacts (user_id, phone) VALUES (1, NULL);
-- 以上语句均可执行成功

2. 插入非法记录（同一user_id下第二条非空phone）：

INSERT INTO user_contacts (user_id, phone) VALUES (1, '2124821212'); -- 第一条非空，执行成功
INSERT INTO user_contacts (user_id, phone) VALUES (1, '1234567890'); -- 第二条非空，触发唯一约束报错

报错信息类似：

ORA-00001: unique constraint (YOUR_SCHEMA.IDX_USER_SINGLE_NON_NULL_PHONE) violated

替代方案：触发器（不推荐） #

虽然可以用触发器实现相同逻辑，但函数索引的性能更优（索引检查是数据库内核级操作，比触发器的行级逻辑更高效）。如果需要了解触发器实现，示例如下：

CREATE OR REPLACE TRIGGER trg_user_phone_unique
BEFORE INSERT OR UPDATE ON user_contacts
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    IF :NEW.phone IS NOT NULL THEN
        SELECT COUNT(*)
        INTO v_count
        FROM user_contacts
        WHERE user_id = :NEW.user_id
          AND phone IS NOT NULL;
        
        IF v_count >= 1 THEN
            RAISE_APPLICATION_ERROR(-20001, '每个用户只能有一条非空电话号码记录');
        END IF;
    END IF;
END;
/

但优先推荐函数索引方案，因为它避免了触发器带来的额外性能开销，且维护更简单。

内容的提问来源于stack exchange，提问作者Patrick