联合约束主键 2020-08-27

在做表约束的时候，需要多个字段进行关联来确定唯一索引：

create index 索引名 on 表名(字段1, 字段2);

上述只是简单的两个字段作为索引，如果此时需要再加一个常见的is_del作为联合约束主键，即当is_del<>1的时候和其他字段一起作为主键：

CREATE UNIQUE INDEX 索引名 ON 表名
(
    NVL2(NULLIF(is_del,1),字段1,NULL),
    NVL2(NULLIF(is_del,1),is_del,NULL),
    NVL2(NULLIF(is_del,1),字段2,NULL)
);

解析：

1）当 is_del=1 时，NULLIF(is_del,1) 则返回null值， 即NVL2(NULLIF(is_del,1),字段1,NULL), 返回的是null值，同理，另外两个NVL2的表达式也是返回null值，因此，三个字段都认为是null值，所以可以允许插入；

2）当 is_del<>1 时，NULLIF(is_del,1)则返回 is_del 值，即NVL2(NULLIF(is_del,1),字段1,NULL)，返回的值是字段1的值，同理，另外两个NVL2的表达式返回的分别是is_del,字段2的值，这时候进行判断是否符合唯一性约束。

3）NULL指的是空值，或者非法值。
NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型；

 

所以对于需要两个字段+一个字段特定值，组成的联合约束主键，可以使用上述方法进行创建。