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

100 阅读 0 评论 66 点赞

我是靠谱客的博主忐忑棒球，最近开发中收集的这篇文章主要介绍oracle的联合约束（多个字段的唯一性），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

简单的唯一性约束的方法

一个表中 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