Oracle数据库中违反唯一约束的处理

根据NULL的定义 NULL表示的是未知 因此两个NULL比较的结果既不相等 也不不等 结果仍然是未知 根据这个定义 多个NULL值的存在应该不违反唯一约束

实际上Oracle也是如此实现的

SQL>CREATE TABLE T (ID NUMBER)

表已创建

SQL>ALTER TABLE T ADD UNIQUE (ID)

表已更改

SQL>INSERT INTO T VALUES ( )

已创建 行

SQL>INSERT INTO T VALUES ( )

INSERT INTO T VALUES ( )

*第 行出现错误:

ORA : 违反唯一约束条件 (YANGTK SYS_C )

SQL>INSERT INTO T VALUES (NULL)

已创建 行

SQL>INSERT INTO T VALUES (NULL)

已创建 行

SQL>INSERT INTO T VALUES (NULL)

已创建 行

但是当唯一约束为复合字段时 则情况发生了变化 根据Oracle文档的描述 对于复合字段的唯一约束 不为空字段的值是不能重复的 也就是说 如果两个字段构成了一个唯一约束 其中一个字段为空 那么另一个字段的值不能出现重复

SQL>DROP TABLE T PURGE

表已删除

SQL>CREATE TABLE T (ID NUMBER ID NUMBER)

表已创建

SQL>ALTER TABLE T ADD UNIQUE (ID ID )

表已更改

SQL>INSERT INTO T VALUES ( )

已创建 行

SQL>INSERT INTO T VALUES ( NULL)

已创建 行

SQL>INSERT INTO T VALUES ( NULL)

已创建 行

SQL>INSERT INTO T VALUES ( NULL)

INSERT INTO T VALUES ( NULL)

*第 行出现错误:

ORA : 违反唯一约束条件 (YANGTK SYS_C )

SQL>INSERT INTO T VALUES (NULL NULL)

已创建 行

SQL>INSERT INTO T VALUES (NULL NULL)

已创建 行

SQL>INSERT INTO T VALUES (NULL NULL)

已创建 行

对于全部为NULL的情况 仍然和单字段唯一约束一样 不会造成重复 但是对于部分为NULL的情况 就如上面例子所示 只要其中不为NULL的部分发生了重复 Oracle就认为约束发生了重复

而这似乎和NULL的定义有所冲突 第一次看concept的时候一直没有搞明白Oracle为什么这么实现 不过这次再看concept的时候 已经想明白了

由于Oracle的唯一约束是依赖索引实现的 而Oracle的BTREE索引又是不存储NULL值的 所以键值全部为NULL的记录不会记录在索引中 因此也就不会违反唯一约束了 而对于部分为NULL的记录 索引是要记录数值的 因此一旦键值中非NULL部分发生了冲突 Oracle就认为违反了的唯一约束

lishixinzhi/Article/program/Oracle/201311/18438

假设要实现约束的列名为C1， 所在表名为MyTable

（1）除了SQL SERVER 以外的大型数据库都是允许 UNIQUE约束有多个空值的。

（2）SQL Server 2008中有了一个解决方案，那就是筛选索引。

CREATE UNIQUE NONCLUSTERED INDEX MyTable

ON MyTable(C1)

WHERE C1 is not null

GO

（3）SQL Server 2008以下版两个方案

A、使用触发器在插入和更新时控制

CREATE trigger Mytrigger on MyTable for insert, update as

BEGIN

IF (select max(cnt) from (select count(i.c1)

as cnt from MyTable, inserted i where MyTable.c1=i.c1 group

by i.c1) x) >1

ROLLBACK TRAN

END

B、 在约束中使用自建函数来实现

创建验证逻辑函数

CREATE FUNCTION [dbo].[fn_CK_MyTable_C1]()

RETURNS BIT

AS

BEGIN

IF(EXISTS(

SELECT1

FROM MyTable AS a

WHERE (C1 IS NOT NULL) AND EXISTS

(SELECT 1 AS Expr1

FROM MyTable

WHERE (C1 IS NOT NULL) AND (C1 = a.C1) )

))

RETURN 0

RETURN 1

END

GO

在约束中引用函数：

ALTER TABLE test_tb

ADD CONSTRAINT CK_MyTable_C1 CHECK (dbo.fn_CK_MyTable_C1() = 1)

GO

---