如何基于关联表字段条件创建带过滤的唯一索引？

首先得明确：你直接写的那种跨表条件的唯一索引，大部分数据库（比如SQL Server、MySQL、PostgreSQL）都是不支持的——因为过滤索引（partial index）只能引用当前表的字段，没法直接关联其他表的字段。不过我们有几种靠谱的替代方案，根据你的场景选就行：

方案一：冗余字段+部分唯一索引（推荐，性能最优） #

这个思路是把表a的serialised字段冗余到表b里，然后基于冗余字段创建索引：

1. 先给表b加个冗余字段：

ALTER TABLE table b ADD COLUMN serialised_from_a BIT;

2. 写个触发器（或者在应用层、存储过程里处理），确保这个字段和表a对应serial_no的serialised值保持一致。比如SQL Server的同步触发器：

CREATE TRIGGER trg_sync_serialised_to_b
ON table a
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE b
    SET b.serialised_from_a = a.serialised
    FROM table b b
    JOIN inserted i ON b.serial_no = i.serial_no;
END

3. 现在就可以创建你想要的唯一索引了：

CREATE UNIQUE INDEX unq_loan_serial_id ON table b(serial_no)
WHERE return_date IS NULL AND serialised_from_a = 1;

这个方案的好处是索引效率拉满，数据库原生支持，缺点是多了个冗余字段，需要维护数据一致性，但触发器能帮你搞定大部分场景。

方案二：触发器直接检查唯一性（无需冗余字段） #

如果不想加冗余字段，那就写个触发器在插入/更新表b的时候，手动检查约束条件：
还是以SQL Server为例，触发器大概是这样：

CREATE TRIGGER trg_b_enforce_unique_serial_rule
ON table b
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查是否违反约束
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN table a ON i.serial_no = a.serial_no
        WHERE a.serialised = 1
          AND i.return_date IS NULL
          -- 排除当前更新的记录（避免更新自身时误判）
          AND EXISTS (
              SELECT 1
              FROM table b b_existing
              WHERE b_existing.serial_no = i.serial_no
                AND b_existing.return_date IS NULL
                AND b_existing.id <> i.id -- 这里替换成表b的主键字段
          )
    )
    BEGIN
        RAISERROR('同一已序列化的编号不能存在多条未归还记录', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

这个方案不用加新字段，但每次插入/更新都会触发查询检查，高并发场景下可能有性能损耗，适合数据量不大的场景。

方案三：数据库特定的函数索引（慎用） #

比如PostgreSQL里可以写个函数去获取表a的serialised值，然后基于这个函数建索引：

CREATE OR REPLACE FUNCTION get_serialised_status(p_serial_no VARCHAR(8))
RETURNS BIT
AS $$
SELECT serialised FROM table a WHERE serial_no = p_serial_no;
$$ LANGUAGE sql STABLE;

CREATE UNIQUE INDEX unq_loan_serial_id ON table b(serial_no)
WHERE return_date IS NULL AND get_serialised_status(serial_no) = 1;

但要注意：如果表a的serialised值被修改了，这个索引不会自动更新（因为函数标记为STABLE，PostgreSQL不会重新计算索引条目），很容易导致数据不一致。除非你能保证表a的serialised字段永远不修改，或者再写个触发器在表a修改时强制更新表b的索引，不然不推荐用这个方案。

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