约束问题总结

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

 

一．约束种类

   首先，来看一下约束的种类，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.