oracle constraints

oracle 的约束主要是在业务逻辑层面维护数据的完整性。主要通过程序员在应用程序中规定约束或者通过定义触发器来维护数据完整性，最后是通过使用oracle自带的约束来维护数据完整性。能使用oracle自带的约束达到要求就尽量使用oracle自带的约束，因为使用触发器等用户自定义约束都会影响数据库的性能。例如：使用触发器时会对表进行锁定并进行表扫描或者索引扫描，这些都会降低数据库性能和并发性。

  oracle 约束主要分为以下几种：

not null 非空约束, unique 唯一约束, primary key 主键约束, foreign key 外键约束, check 约束。

not null 非空约束：

  创建方式：1, 建表时在列级别定义(也就是只能在定义表时将约束的定义写在该列后面),2种方式。一种使用自定义约束名称，一种是使用系统默认名称。

view plaincopy to clipboardprint?
create table t  
(  
  tid     number(8) constraint NK_t1 not null,  
  tname   varchar2(10) not null 
) 
create table t
(
  tid     number(8) constraint NK_t1 not null,
  tname   varchar2(10) not null
)

view plaincopy to clipboardprint?
SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;  
   
CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE  
--------------- ------------------------------ -------- --------------  
NK_T1           T                              ENABLED  NOT DEFERRABLE  
SYS_C003735     T                              ENABLED  NOT DEFERRABLE  
   
SQL>  
SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;

CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE
--------------- ------------------------------ -------- --------------
NK_T1           T                              ENABLED  NOT DEFERRABLE
SYS_C003735     T                              ENABLED  NOT DEFERRABLE

SQL> 

             2，在表建立后对表进行修改，但是要确保表中数据没有违反约束。

view plaincopy to clipboardprint?
SQL> alter table t modify tid not null;  
   
Table altered  
   
SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;  
   
CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE  
--------------- ------------------------------ -------- --------------  
SYS_C003736     T                              ENABLED  NOT DEFERRABLE  
   
SQL>  
SQL> alter table t modify tid not null;

Table altered

SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;

CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE
--------------- ------------------------------ -------- --------------
SYS_C003736     T                              ENABLED  NOT DEFERRABLE

SQL> 

check 约束

  创建方式：可以在表级别和列级别进行定义(既可以在列后面定义也可以在列定义完后再定义)。也是2种定义方式。

view plaincopy to clipboardprint?
SQL> create table t  
  2  (  
  3    tid     number(8) ,  
  4    tname   varchar2(10),  
  5    constraint CK_T1 check ((tid is not null) and (tid > 0))  
  6  )  
  7  /  
   
Table created  
   
SQL> alter table t add constraint CK_T2 check(tname is not null);  
   
Table altered  
   
SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;  
   
CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE  
--------------- ------------------------------ -------- --------------  
CK_T1           T                              ENABLED  NOT DEFERRABLE  
CK_T2           T                              ENABLED  NOT DEFERRABLE  
   
SQL>  
SQL> create table t
  2  (
  3    tid     number(8) ,
  4    tname   varchar2(10),
  5    constraint CK_T1 check ((tid is not null) and (tid > 0))
  6  )
  7  /

Table created

SQL> alter table t add constraint CK_T2 check(tname is not null);

Table altered

SQL> select t.***straint_name, t.***le_name, t.status, t.***errable from user_constraints t;

CONSTRAINT_NAME TABLE_NAME                     STATUS   DEFERRABLE
--------------- ------------------------------ -------- --------------
CK_T1           T                              ENABLED  NOT DEFERRABLE
CK_T2           T                              ENABLED  NOT DEFERRABLE

SQL> 

约束CK_T1保证tid列不能为空并且要大于0，CK_T2保证iname不能为空。check约束也可以是同一行的不同列之间的规则。

unique 约束

  创建方式：单unique规定的列只有一列时可以在列级别定义，如果unique规定的列包含多列时只能在表级别定义。

view plaincopy to clipboardprint?
SQL> create table t2  
  2  (  
  3     vid   number constraint VK_T1 unique,  
  4     vname varchar2(10),  
  5     vsex  varchar2(10),  
  6     constraint VK_T2 unique(vname,vsex)  
  7  )  
  8  /  
   
Table created  
 
SQL> select t.***straint_name, t.***le_name, t.status, t.***idated, t.***errable from user_constraints t;  
   
CONSTRAINT_NAME TABLE_NAME STATUS   VALIDATED  DEFERRABLE  
--------------- ---------- -------- ---------- --------------  
VK_T1           T2         ENABLED  VALIDATED  NOT DEFERRABLE  
VK_T2           T2         ENABLED  VALIDATED  NOT DEFERRABLE  
   
SQL>  
SQL> create table t2
  2  (
  3     vid   number constraint VK_T1 unique,
  4     vname varchar2(10),
  5     vsex  varchar2(10),
  6     constraint VK_T2 unique(vname,vsex)
  7  )
  8  /

Table created

SQL> select t.***straint_name, t.***le_name, t.status, t.***idated, t.***errable from user_constraints t;

CONSTRAINT_NAME TABLE_NAME STATUS   VALIDATED  DEFERRABLE
--------------- ---------- -------- ---------- --------------
VK_T1           T2         ENABLED  VALIDATED  NOT DEFERRABLE
VK_T2           T2         ENABLED  VALIDATED  NOT DEFERRABLE

SQL> 

unique约束创建的同时会产生一条索引(可能是唯一性所以也可以是非唯一性索引，这要看建表时指定该表是否在数据插入时立即检查数据的约束)：

view plaincopy to clipboardprint?
SQL> select t.***ex_name, t.***le_name, t.***queness from user_indexes t;  
   
INDEX_NAME  TABLE_NAME UNIQUENESS  
----------- ---------- ----------  
VK_T1       T2         UNIQUE  
VK_T2       T2         UNIQUE 
SQL> select t.***ex_name, t.***le_name, t.***queness from user_indexes t;

INDEX_NAME  TABLE_NAME UNIQUENESS
----------- ---------- ----------
VK_T1       T2         UNIQUE
VK_T2       T2         UNIQUE

由于有索引所以在创建表时可以指定索引存储的位置和一些存储参数。

view plaincopy to clipboardprint?
SQL> create table t2  
  2  (  
  3     vid   number constraint VK_T1 unique,  
  4     vname varchar2(10),  
  5     vsex  varchar2(10),  
  6     constraint VK_T2 unique(vname,vsex) using index tablespace indx  
  7                                         storage(initial 100k next 100k pctincrease 0)  
  8                                         nologging  
  9  )  
10  /  
   
Table created 
SQL> create table t2
  2  (
  3     vid   number constraint VK_T1 unique,
  4     vname varchar2(10),
  5     vsex  varchar2(10),
  6     constraint VK_T2 unique(vname,vsex) using index tablespace indx
  7                                         storage(initial 100k next 100k pctincrease 0)
  8                                         nologging
  9  )
10  /

Table created

指定约束索引存储的表空间是indx表空间，初始块大小是100k，然后对dml操作不产生日志(但是由于其他原因也会产生日志，只是相对默认的logging要少)

primary key 主键约束

   创建方式:primary key主要是由非空和唯一性组合而成的。一个表只能包含一个主键，但一个主键可以含有多列。

view plaincopy to clipboardprint?
SQL> create table t2  
  2  (  
  3     vid   number constraint VK_T1 unique,  
  4     vname varchar2(10),  
  5     vsex  varchar2(10),  
  6     constraint VK_T2 primary key(vname,vsex) using index tablespace indx  
  7                                         storage(initial 100k next 100k pctincrease 0)  
  8                                         nologging  
  9  )  
10  /  
   
Table created 
SQL> create table t2
  2  (
  3     vid   number constraint VK_T1 unique,
  4     vname varchar2(10),
  5     vsex  varchar2(10),
  6     constraint VK_T2 primary key(vname,vsex) using index tablespace indx
  7                                         storage(initial 100k next 100k pctincrease 0)
  8                                         nologging
  9  )
10  /

Table created

foreign key 外键

  创建方式:外键涉及的表可以有2张也可以是1张，2张的情况下一张child表中的一个字段引用的键必须fater表中的主键。约束是建立在child表中的，标示该表中的该字段中的值必须在父表中存在或者是NULL值。

view plaincopy to clipboardprint?
SQL> create table dept  
  2  (  
  3    did   number(8),  
  4    dname varchar2(20),  
  5    constraint PK_DEPT primary key (did)  
  6  )  
  7  /  
   
Table created  
   
SQL>   
SQL> create table emp  
  2  (  
  3    eid   number(8) primary key,  
  4    ename varchar2(20),  
  5    did   number(8) /*references dept(did)*/,  
  6    dname varchar2(20),  
  7    constraint FK_EMP2 foreign key(did) references dept(did)  
  8  )  
  9  /  
   
Table created 
SQL> create table dept
  2  (
  3    did   number(8),
  4    dname varchar2(20),
  5    constraint PK_DEPT primary key (did)
  6  )
  7  /

Table created

SQL>
SQL> create table emp
  2  (
  3    eid   number(8) primary key,
  4    ename varchar2(20),
  5    did   number(8) /*references dept(did)*/,
  6    dname varchar2(20),
  7    constraint FK_EMP2 foreign key(did) references dept(did)
  8  )
  9  /

Table created

由于外键(foreign key)由reference key(引用键)决定所以在对fater table进行update或者drop,delete等操作时会受到限制。具体有外键约束的状态决定。

  delete on action, delete cascade, delete set null

delete on action 默认设置，如果删除fater table表中的数据，这时oracle会锁定字表然后进行扫描表(如果外键列有所以则扫描索引)然后child表中的外键列存在该数据则不允许删除。

delete cascade 则把字表中的相应对应的行也删除。

delete set null 则吧子表中的外键列对应的数据变成NULL.

view plaincopy to clipboardprint?
create table emp  
(  
  eid   number(8) primary key,  
  ename varchar2(20),  
  did   number(8) /*references dept(did)*/,  
  dname varchar2(20),  
  constraint FK_EMP2 foreign key(did) references dept(did) on delete set null 
) 
create table emp
(
  eid   number(8) primary key,
  ename varchar2(20),
  did   number(8) /*references dept(did)*/,
  dname varchar2(20),
  constraint FK_EMP2 foreign key(did) references dept(did) on delete set null
)