Oracle数据库开发——表（数据完整性约束）

创建表时候，都要考虑到数据完整性，所以先提一下，数据完整性约束：
主键约束(primary key)、唯一性约束(unique)、检查约束(check)、外键约束(foreign key)、非空约束(not null)。

主键约束(primary key)：主键可以确保一个表中没有重复的键值数据行。作为主键的列或者列的组合，其值必须唯一，且不能为NULL。
   一个表只能定义一个主键约束，同时Oracle Database自动为主键列建立一个唯一性索引，用户可以为该索引指定存储位置和存储参数。
   主键约束可以定义在列级，也可以定义在表级。
   由多列组成的主键叫做复合主键，一个复合主键中的列的数量不能超过32个。
   
唯一性约束(unique)：确保列或列的组合具有唯一值(若没有非空约束，这个值是可以为空的)，Oracle Database自动为主键列建立一个唯一性索引，用户可以为该索引指定存储位置和存储参数。
   唯一性约束可以定义在列级，也可以定义在表级。

检查约束(check)：检查约束用来限制列的取值范围，实现对数据的自动检查。
  一个列可以定义多个检查约束，其表达式中必须引用相应的列，且表达式中不能包含子查询，SYSDATE/USER等SQL函数和ROWID、ROWNUM等伪劣。
  检查约束可以定义在列级或表级。
  
外键约束(foreign key)：外键约束的定义使得数据库中表与表之间建立了父子关系。外键约束用来定义子表中列的取值只能是父表中参照列的值，或者为空。
  父表中被参照的列必须有唯一性约束或主键约束，外键约束可以定义在一列或多列组合上，可以定义在列级或表级。
  外键可以是自参照约束，即外键可以指向同一个表。

非空约束(not null)：非空约束限制列的取值不能为NULL，一个表中可以定义多个非空约束。非空约束只能定义在列级。

例1：验证列主键系统自动创建唯一索引
创建一个订单表，订单号为主键：
create table tab_orders
( order_id varchar2(20) constraint O_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(12)
,goods_id number(6)
);

查看系统是否为主键列自动创建了唯一索引?

SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'O_PK';
 
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
O_PK                           NORMAL                      TAB_ORDERS                     UNIQUE
 
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_ORDERS';
 
CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
O_PK                           P               O_PK 

结果显示系统自动创建了唯一索引。

例2： 表级主键——联合主键
上面一个是列级约束，那么表级约束是怎么建立的？
其实表级约束通常是针对多个列建立的约束，因此，表级约束的定义独立于列的定义。以( ，)形式
create table tab_bookauthors
(author_id varchar2(15)
,book_id number(6)
,author_ord number
,constraint BA_PK primary key (author_id,author_ord)
);

SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'BA_PK';
 
INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
BA_PK                          NORMAL                      TAB_BOOKAUTHORS                UNIQUE
 
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_BOOKAUTHORS';
 
CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
BA_PK                          P               BA_PK
 
SQL> select * from user_ind_columns z where z.INDEX_NAME = 'BA_PK';
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ --------------- --------------- ------------- ----------- -------
BA_PK                          TAB_BOOKAUTHORS                AUTHOR_ID                     1            15          15 ASC
BA_PK                          TAB_BOOKAUTHORS                AUTHOR_ORD                    2            22           0 ASC


中间休息了两天，下面继续...

约束的定义 ：
约束的定义，约束可以在创建表的时候定义，也可以在表创建完整之后，单独添加。
create table中定义约束的语法：
定义列级约束:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
);

带有定义表级约束的:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
,[constraint constraint_name] constraint_type ([column1,column2,...]|[condition])
);

先创建表,然后添加约束
create table [schema].table_name(...);

alter table [schema].table_name add constraint constraint_name constraint_type (column1,column2)[condition];

个人感觉上先创建表，然后去添加更好一些，感觉比较清晰吧= =#。
习惯上，NOT NULL 、unique、 check 约束直接在创建表的时候定义，然后单独去定义主外键约束。
还是看看oracle是怎么保存的吧
例3：
create table demo1
(col1 varchar2(4) constraint DO1_PK primary key
,col2 varchar2(10) unique
,col3 number not null
)
;
create table demo2
(col1 varchar2(4)
,col2 varchar2(10) 
,col3 number not null
);
alter table demo2 add constraint DO2_PK primary key(col1);
alter table demo2 add constraint DO2_UI unique(col2); 

用PL/SQL查看建表语句
-- Create table
create table DEMO1
(
  COL1 VARCHAR2(4) not null,
  COL2 VARCHAR2(10),
  COL3 NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEMO1
  add constraint DO1_PK primary key (COL1)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;
alter table DEMO1
  add unique (COL2)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;
================================================================== 
-- Create table
create table DEMO2
(
  COL1 VARCHAR2(4) not null,
  COL2 VARCHAR2(10),
  COL3 NUMBER not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEMO2
  add constraint DO2_PK primary key (COL1)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;
alter table DEMO2
  add constraint DO2_UI unique (COL2)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;
  
这里可以看到表demo1在PL/SQL里面保存的语句被拆分开了
在查看下用户视图
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('DEMO1','DEMO2');
 
TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
DEMO1                          SYS_C003893                    C               
DEMO1                          DO1_PK                         P               DO1_PK
DEMO1                          SYS_C003895                    U               SYS_C003895
DEMO2                          SYS_C003891                    C               
DEMO2                          DO2_PK                         P               DO2_PK
DEMO2                          DO2_UI                         U               DO2_UI

这里第二列SYS开头的是系统定义唯一标示名，因为用户没有定义约束名。最后一列SYS开头是索引名唯一标示名。

看到这里，我的感觉是虽然创建方式不同，但是oracle保存方式是相同的。
通常先create table，再alter add约束，用户会给约束起名字(constraint)，需要系统创建唯一索引的约束，索引名与约束名相同。
直接创建的约束，有时候为了建表语句书写的简便(或者用户忽略忘记了)，添加约束名，系统会默认分给约束一个唯一标示名，需要系统创建唯一索引的约束，索引名与约束名相同。
以上是个人观点，有不对的地方请指正。


添加约束
例4:
create table employees
(emp_id char(10)
,ename varchar2(20)
,job_id number(6)
,job_lv integer
,pub_id char(4)
,hiredate date
);
create table jobs
(jobid number(6) primary key
,jobname varchar2(20) not null
,minlvl integer
,maxlvl integer
);
--为employee的emp_id添加一个主键约束
alter table employees add constraint E_PK primary key(emp_id);

--为employee的job_id添加外键，参照表为jobs的job_id
alter table employees add constraint E_FK foreign key(job_id) references jobs(jobid) on delete cascade;

--为employee的ename添加非空约束
alter table employees modify ename constraint E_NN not null;

注意:当为表添加非空约束时，必须使用modify子句，而不是add子句。


删除约束
--删除employees的主键约束
alter table employees drop constraint E_PK;  --指定约束名删除约束

--也可以用
alter table employees drop primary key;    --指定约束内容删除约束

注意,删除主键约束、唯一约束的同时系统为自动删除对应的唯一性索引。如果想保留唯一性索引,可以使用keep index子句。
   alter table employees drop constraint E_PK keep index;
   如果主键被其他外键参照，可以使用cascade关键字指出删除主键约束的同时把参照该主键的外键一起删除。
alter table employees drop constraint E_PK cascade;

下面来验证
例5: 
接着例4，先查看EMPLOYEES，JOBS的约束视图
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES                      E_PK                           P               E_PK
EMPLOYEES                      E_FK                           R               
EMPLOYEES                      E_NN                           C               
JOBS                           SYS_C003897                    C               
JOBS                           SYS_C003898                    P               SYS_C003898


SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS                           NORMAL                      JOBS                           UNIQUE
EMPLOYEES                      NORMAL                      EMPLOYEES                      UNIQUE


然后删除EMPLOYEES的主键
alter table employees drop constraint E_PK;

SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES                      E_FK                           R               
EMPLOYEES                      E_NN                           C               
JOBS                           SYS_C003897                    C               
JOBS                           SYS_C003898                    P               SYS_C003898


SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS                           NORMAL                      JOBS                           UNIQUE

结果可以看到constraint E_PK 对应的唯一索引被删除了。


下面删除jobs的主键jobid，表employees的外键参照该列
SQL> alter table jobs drop primary key cascade;
 
Table altered
 
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES                      E_NN                           C               
JOBS                           SYS_C003897                    C               
 
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
 
TABLE_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------

结果验证了，如果主键被其他外键参照，使用cascade关键字指出删除主键约束的同时把参照该主键的外键一起删除。

约束的状态和延迟检查

(1)约束的状态
Oracle数据库表中的约束出于激活状态，即约束对表的插入或更新操作进行检验，不符合约束的操作被回退。但是，实际工作中，我们有时候
需要将约束的状态置为禁用，这样可以提高效率。
使用 alter table ... disable ...语句

语法: alter table [schema.]table_name
                modify constraint constraint_name {enable|disable};
                
需要注意的几点:
1.禁用主键使用 alter table ... disable ... cascade;后面的cascade是为了防止主键被其他表引用为外键，而导致该主键约束无法禁用。
2.禁用primary key 和unique约束时，系统会将它们对应的唯一性索引删除，并且，在重新激活时，系统重新建立相应的唯一性索引。
 如果希望保留唯一性索引，可以在禁用时候，使用keep index子句。

(2)约束的检查状态
约束的激活和禁用状态只对设置状态后的数据操作起作用，要想对表中已存在的数据也进行约束检查，则必须结合"检查状态"——validate(检查)
和novaildate(非检查)。

语法:alter table [schema.]table_name
                modify constraint constraint_name validate|novalidate;

结合，激活 和 禁用:
组合一下
enable validate :激活检查状态，Oracle数据库默认的检查状态。对表所有都进行约束条件检查。
enable novalidate : 激活非检查状态，只对更新或新插入的数据进行约束检查，不对已存在数据进行约束检查。
disable validate :禁用检查状态，在该状态下Oralce数据库不允许用户进行更新或者插入操作。(因为约束已禁用却又要求检查，无法检查)
disable novalidate : 禁用非检查状态，不对数据进行约束检查。

(3)约束的延迟检查

立即约束检查 initially immediate: 默认为该选项，执行外一个DML语句后立即检验

延迟约束检查 initially deferred: 当 事务提交或调用set constraint immediate语句时才检验。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28929558/viewspace-1144931/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28929558/viewspace-1144931/