Oracle约束问题总结

刚开始接触约束时，可能对它产生了一些困惑，比如说一张表中可以有几个不同的约束，一张表中的同一列可以有几个不同的约束，还有约束在列级和表级定义等等问题。

一．约束种类

首先，来看一下约束的种类，ORACLE支持五种类型的完整性约束:

1.NOT NULL (非空)——防止NULL值进入指定的列，在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值；

2.CHECK (检查)--检查在约束中指定的条件是否得到了满足；

3.UNIQUE (唯一)——保证在指定的列中没有重复值，在该表中每一个值或者每一组值都将是唯一的，但允许NULL插入；

4.PRIMARY KEY (主键)——用来唯一的标识出表的每一行,并且防止出现NULL值；

5.FOREIGN KEY (外部键)——通过使用公共列在表之间建立一种父子(parent-child)关系。

二．约束的定义

约束的定义分为列级和表级；

1.对于NOT NULL约束，它只能在列级定义；
如下：
SQL> create table dept(deptno number not null);
Table created.

在表级定义是不正确的：

SQL> create table dept(deptno number(2,0),
  2  constraint dept_deptno_notnull  not null(deptno));
constraint dept_deptno_notnull  not null(deptno))
                                *
ERROR at line 2:
ORA-00904: : invalid identifier
或者使用alter  table 命令来添加约束：
SQL> create table dept(deptno number(2,0));
Table created.
SQL> alter table dept modify deptno not null;
Table altered.


2.对于CHECK约束，它可以在表级和列级定义；

列级定义如下：
SQL> create  table dept(deptno  number(2,0)  constraint dept_deptno_ck  check(deptno   between 10  and  999));
Table created.

对于表级定义如下：
SQL> create  table dept(deptno  number(2,0),
  2   constraint dept_deptno_ck  check(deptno   between 10  and  999));
Table created.


或者使用alter  table 命令来添加约束：
SQL> create table dept(deptno  number(2,0));
Table created.
SQL> alter table dept add constraint dept_deptno_ck check(deptno between  10  and 999);
Table altered.

3.对于UNIQUE约束，它可以在表级和列级定义；

列级定义如下：
SQL> create table dept(deptno number(2,0) constraint dept_deptno_uq unique);
Table created.

对于表级定义如下：
SQL> create table dept(deptno number(2,0),
  2  constraint dept_deptno_uq unique(deptno));
Table created.


或者使用alter  table 命令来添加约束：
SQL> create table dept(deptno number(2,0));
Table created.
SQL> alter table dept add constraint dept_deptno_uq unique(deptno);
Table altered.

4.对于PRIMARY KEY约束，它可以在表级和列级定义；

列级定义如下：

SQL> create  table dept(deptno number(2,0)  constraint dept_deptno_pk  primary key); 
Table created.


对于表级定义如下：

SQL> create  table dept(deptno number(2,0) , 
  2  constraint dept_deptno_pk primary key(deptno));
Table created.

或者使用alter  table 命令来添加约束：

SQL> create table dept(deptno number(2,0));
Table created.
SQL> alter table dept add constraint dept_deptno_pk  primary  key(deptno);
Table altered.

5.对于FOREIGN KEY约束，它可以在表级和列级定义；

列级定义如下：
SQL> create table dept(deptno  number(2,0)   constraint dept_deptno_pk  primary key);
Table created.
SQL> create  table  dept1(deptno  number(2,0)   constraint dept1_deptno_fk  references dept(deptno));
Table created.

表级定义如下：
SQL> create table  dept(deptno  number(2,0)   constraint   dept_deptno_pk  primary  key);
Table created.
SQL> create  table  dept1(deptno  number(2,0),
  2  constraint dept1_deptno_fk  foreign   key(deptno)   references dept(deptno));
Table created.

或者使用alter  table 命令来添加约束：
SQL> create table  dept(deptno  number(2,0)   constraint   dept_deptno_pk  primary  key);
Table created.
SQL> create  table dept1(deptno number(2,0));
Table created.
SQL> alter table dept1  add constraint dept1_deptno_fk  foreign  key(deptno)  references  dept(deptno);
Table altered.

三．约束问题总结

1.NOT NULL约束，如果要求一组列都具这个约束，则不能为整个组定义NOT NULL约束，而必须对每个列单独定义NOT NULL约束；

2.CHECK约束，无法使用子查询来计算值是否被允许，也无法使用诸如SYSDATE函数；

3.UNIQUE约束，可以对单列或者多列定义，对于多列时，不需要这些列数据类型相同，也不需要相邻；

4.PRIMARY KEY约束，可以对单列或者多列定义，它相当于UNIQUE和NOT NULL的组合，但是每个表中只能有一个PK约束，但是可以有任意数量的UNIQUE和NOT NULL的约束列；

5.对于FOREIGN KEY约束，这个约束在子表中定义的列（或者多列）对应父表中的主键列。这些列不需要同名，但数据类型必须相同。什么样的表可以作为父表？这个要具有UNIQUE或者是PRIMARY KEY约束的表。FOREIGN KEY约束还有个特点，就是可以插入NULL值，即使在父表中没有NULL值的情况下。

6.对于开始的问题，一张表中可以有几个不同的约束，这个可以分为两种情况，第一，在表中不同列的约束，如果在不同列有两个PRIMARY KEY是不允许的，其它情况都是可以的，比如可以一列是PK，一列是UNIQUE。第二，在表中同一列中，如果同一列同时有PRIMARY KEY和UNIQUE是不允许的，其它情况都是可以的，比如这一列既是UNIQUE又是FOREIGN KEY。

7.对于创建约束时自动创建索引问题补充

这个是对上次写的补充，（主键约束自动建立索引问题）

在上面所说的五种约束中，只有PK和UNIQUE约束在创建时，产生唯一性索引，

SQL> create table  dept(deptno  number(2,0)   constraint   dept_deptno_pk  primary  key);
SQL> select index_name  from  user_indexes;
INDEX_NAME
------------------------------
DEPT_DEPTNO_PK
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX

可以看到产生了索引；

在dept表deptno列上创建索引，会产生错误，是因为在建立PRIMARY KEY约束时已经创建了索引；

SQL> create index  tt  on  dept(deptno);
create index  tt  on  dept(deptno)
                           *
ERROR at line 1:
ORA-01408: such column list already indexed

把两个列作为主键，

SQL> create  table dept(deptno   number(2,0),dname varchar2(12),constraint deptno_dname_pk  primary  key(deptno,dname));
Table created.

在上表中的其中一列建立索引，可以成功。

SQL> create  index  tt  on  dept(dname);
Index created.