唯一约束
Oracle从入门到总裁:https://blog.csdn.net/weixin_67859959/article/details/135209645
唯一约束
唯一约束的特点是在某一个列上的内容不允许出现重复。
例如，现在要收集用户的信息，假设包含编号（mid）、姓名（name）、E-mail（email）
很明显 email 的数据不可能重复，所以就可以使用 unique约束完成
-- 删除数据表
drop table member purge;
-- 创建数据表
create table member(
mid number,
name varchar2(20) not null,
email varchar2(20) unique
);SQL> set linesize 250
SQL> -- 删除数据表
SQL> drop table member purge;
表已删除。
SQL> -- 创建数据表
SQL> create table member(
2 mid number,
3 name varchar2(20) not null,
4 email varchar2(20) unique
5 );
表已创建。
上面数据表中添加了 email 字段为唯一约束
下面来看一下这个约束的作用。首先向该表添加一个数据
SQL> INSERT INTO member(mid,name,email) VALUES (3,'张三','hello@hello.hello') ;
已创建 1 行。
此时该行数据正确地添加到数据表中，然后再继续添加下面这条语句
可以看出 email 这个字段的值仍然是 hello@hello.hello，这就违反了唯一约束的限制
SQL> INSERT INTO member(mid,name,email) VALUES (1,'李四','hello@hello.hello') ;
INSERT INTO member(mid,name,email) VALUES (1,'李四','hello@hello.hello')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C008261)
在 Oracle 中约束本身也称为一个对象，也就是说只要你设置了约束，那么 Oracle 会自动为你创建与之相关的约束对象信息
而这些过程都是自动完成的
既然是对象，所有的对象都会在数据字典中进行保存，用户的约束数据字典应该使用 user_constraint
COL owner FOR A30 ;
COL constraint_name FOR A30 ;
COL table_name FOR A30 ;
SELECT owner,constraint_name,table_name FROM user_constraints ;SQL> SELECT owner,constraint_name,table_name FROM user_constraints ;
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
FK_DEPTNO
EMP
SCOTT
PK_EMP
EMP
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
SYS_C008233
PRODUCT
SCOTT
PK_DEPT
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPT
SCOTT
SYS_C008234
CUSTOMER
SCOTT
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_C008260
MEMBER
SCOTT
SYS_C008261
MEMBER
已选择 7 行。我们可以发现“user_constraints”数据字典只是告诉用户约束属于那张表
但是并没有告诉用户约束具体是哪个列上的，所以此时可以利用另外一个数据字典 user_cons_column
COL owner FOR A30 ;
COL constraint_name FOR A20 ;
COL table_name FOR A20 ;
COL column_name FOR A30 ;
SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns ;
SQL> SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns ;
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
PK_EMP
EMP
EMPNO
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
FK_DEPTNO
EMP
DEPTNO
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
SYS_C008233
PRODUCT
PRODUCTNAME
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
PK_DEPT
DEPT
DEPTNO
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
SYS_C008234
CUSTOMER
NAME
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
SYS_C008260
MEMBER
NAME
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT
SYS_C008261
MEMBER
EMAIL
已选择 7 行。
唯一约束并不像非空约束那样，可以很明确地告诉用户是哪个列上出现问题
所以为了解决这样的错误，可以采用“约束简写 _ 字段”来定义这个约束
例如唯一约束的简写是“UK”，现在是在 email 字段上设置了唯一约束
所以可以使用“uk_email”来作为此约束的名字。如果要指定名字，则必须在约束创建的时候完成，利用 CONSTRAINT 关键字定
-- 产出数据表
drop table member purge;
-- 创建数据表
create table member(
mid number,
name varchar2(20) not null,
email varchar2(20),
constraint uk_email unique(email)
);SQL> create table member(
2 mid number,
3 name varchar2(20)not null,
4 email varchar2(20),
5 constraint uk_email unique(email)
6 );
表已创建。从现在开始，只要是进行数据表创建，约束一定要设置名字。约束的名字绝对不能够重复
如果说现在设置了唯一约束，但是保存的是 null 呢？null 并不在唯一约束的判断范畴之中
也就是说当保存的是 null 的时候，不认为违反了唯一约束条
