oracle的联合约束（多个字段的唯一性）

客官°小女子只卖身不卖艺 2022-07-14 02:44 483阅读 0赞

### 简单的唯一性约束的方法 ###

一个表中 2个字段的联合约束

创建数据表  
create table zcz (id number not null,name varchar(20) null, age varchar(20) null);  
  
创建主键 或者联合主键 但一个表中只能有一个主键  
alter table zcz add constraints zcz\_pk primary key (id);  
alter table zcz add constraints zcz\_name\_age primary key (name,age);  
  
  
创建联合约束（下面该条sql的作用是name和age不能同时相等）

CREATE UNIQUE INDEX zcz\_name\_age ON zcz(NVL2(name,age,NULL),NVL2(name,name,NULL));

### 比较复杂的唯一性约束的方法 ###

今天在做表约束修改的时候遇到一个系统需求，假设在同一张表 T 中，有 a，b，c 三个字段，另外有主键已经定义好的，现在要求，只要当符合 b<>3 的条件的时候，a，b，c 三个字段联合唯一，解决方法如下：

创建表的唯一性索引

CREATE UNIQUE INDEX idx\_ut ON t(NVL2(NULLIF(b,3),a,NULL),NVL2(NULLIF(b,3),b,NULL),NVL2(NULLIF(b,3),c,NULL));

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

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

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