資料完整性約束：主鍵、外來鍵、各種約束的建立刪除語句

ORACLE對資料庫完整性的約束：

三種方法維護資料完整性：ORACLE完整性約束，資料庫觸發器，應用程式程式碼。

應儘量使用ORACLE完整性約束，可靠性和效率高，容易修改，使用靈活，記錄在資料字典。

ORACLE五種約束：

非空 not null,定義 的列不能為空。只能在列級定義

唯一，unique，表中每一行所定義 的列或列值不能相同     

主鍵primary key   不能包括空值，主鍵唯一標識表中每一行，一列或幾列組合為主鍵

外來鍵foreign key    指明一列或幾列的組合為外來鍵以維護從表chilD table和主表 parent table 之間的引用完整性referential integrity 

條件約束check，表中每一行要滿足約束條件。約束加上表上，建立表時可以定義

1.查詢約束

查詢表中是否有約束並顯示約束名：顯示表列所對應的約束的資訊

BYS@bys1>select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA');
Enter value for aa: emp
old   1: select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('&AA')
new   1: select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.table_name = upper('emp')

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ---------------
PK_EMPNO                       EMPNO
查表中是否有主鍵約束

BYS@bys1>select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name');
Enter value for table_name: emp
old   1: select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('&table_name')
new   1: select a.constraint_name,  a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name =upper('emp')

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ ---------------
PK_EMPNO                       EMPNO

查當前使用者下的所有約束的資訊

BYS@bys1>col owner for a10
BYS@bys1>col table_name for a10
BYS@bys1>select * from user_cons_columns;
OWNER      CONSTRAINT_NAME                TABLE_NAME COLUMN_NAME       POSITION
---------- ------------------------------ ---------- --------------- ----------
BYS        PK_OBJ_ID                      TEST2      OBJECT_ID                1
BYS        SYS_C0011203                   TEST1      OBJECT_ID                1
BYS        PK_EMPNO                       EMP        EMPNO                    1

查詢當前使用者的相關約束的狀態資訊，可以查dba_constraints或USER_constraints  

BYS@bys1>select  constraint_name,table_name,constraint_type,status,deferrable,deferred,validated  from dba_constraints where owner='BYS';
CONSTRAINT_NAME                TABLE_NAME C STATUS   DEFERRABLE     DEFERRED  VALIDATED
------------------------------ ---------- - -------- -------------- --------- -------------
SYS_C0011203                   TEST1      P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMPNO                       EMP        P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
PK_OBJ_ID                      TEST2      P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

SCOTT@bys1>select  constraint_name,table_name,constraint_type,status,deferrable,deferred,validated  from user_constraints;
CONSTRAINT_NAME                TABLE_NAME C STATUS   DEFERRABLE     DEFERRED  VALIDATED
------------------------------ ---------- - -------- -------------- --------- -------------
FK_DEPTNO                      EMP        R ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
PK_DEPT                        DEPT       P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
PK_EMP                         EMP        P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
SYS_C0011265                   TEST       P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED
PK_A                           TEST1      P ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

2.增加、刪除、修改約束

增加與修改：

增刪主鍵及外來鍵

alter table dept add constraint pk_dept primary key(deptno);

alter table dept2 add primary key(dname); 不指定約束名，則由系統自動命令約束名。
alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade;   
alter table emp add constraint fk_deptno foreign key(deptno) references dept(deptno);

注：on delete cascade和on delete set null的作用是用來處理級聯刪除問題的，

如果你需要刪除的資料被其他資料所參照(主、外來鍵），那麼你應該決定到底希望oracle怎麼處理那些參照這些即將要刪除資料的資料的.

你可以有三種方式：
禁止刪除，這也是oracle預設的。
將那些參照本值的資料的對應列賦空，使用on delete set null關鍵字。即刪除本表的列中的值，對本列有外來鍵引用的表的相應的列有相應值行會被修改為NULL。
將那些參照本值的資料一併刪除，使用on delete cascade關鍵字，即刪除本表的列中的值，對本列有外來鍵引用的表的相應的列有相應值行會被刪除

增加CHECK  約束:

alter table dept add constraint  valid_deptno check (deptno<5000);       DEPTNO列的值需要少於5000

SQL>alter table customer add constraint abc check (address in (’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’));           增加客戶的住址只能是’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’；

增加惟一約束

alter table customer add constraint  aaa unique(cardId);

增加非空約束--注意增加非空約束時用的是關鍵字modify，其它四種約束都是ADD

SQL>alter table dept modify dname not null;

刪除

對非空約束的刪除：因為非空約束不能指定約束名，應該先通過查詢表和列所對應的的約束資訊，找出約束名，再刪除。如下：

BYS@bys1>alter table dept drop constraint SYS_C0011725; 

修改約束--大可以刪除了重建

alter table dept2 modify constraint pk_d2 initially immediate;

刪除指定名字的約束---可以是主鍵、外建或其它約束的名字

alter table emp drop constraint PK_EMPNO;

alter table scott.event drop constraint evtid_pk;   刪除其它使用者的約束--需要DBA許可權

不用指定約束名，直接刪除表中主鍵

alter table emp drop primary key cascade;   刪除主鍵約束的時候，如果在些主鍵上的外來鍵建立時未指定on delete cascade引數，直接刪除主鍵報錯，要加上cascade引數。

重命令約束

alter table scott.emp rename constraint  pk_emp toemp_empno_pk;

3.修改完整性約束的狀態

完整性約束的四種狀態

為滿足臨時違反CONSTRAINT的資料需要，引入states概念
CONSTRAINT可以ENABLE/DISABLE, ENABLE表示資料在INSERT/UPDATE前會被檢查，DISABLE則不作檢查。

disable novalidate 禁止而無效，新入資料和舊有資料都未經CONSTRAINT檢測。即關閉了約束。

enable novalidate 啟用而無效 ， 即舊有資料狀態未知，不一定滿足CONSTRAINT。新入資料需經CONSTRAINT檢測。

disable validate     禁止而有效，表中由不能做任何更改，即無法對錶中約束所限制的列進行任何DML操作，因為操作可能會使已VALIDATE的資料失去一致性。

enable validate      啟用而有效  即新入資料和舊有資料都經CONSTRAINT檢測，滿足CONSTRAINT條件。 這是預設引數。

延遲性DEFERRED約束，僅在事務TRANSACTION提交時進行資料完整性檢查，如違反，整個事務進行回滾。

非延遲性NONDEFERRED約束。立即性約束IMMEDIATE，在每個DML語句結束時進行完整性檢查，違反條件的語句將被回滾。預設引數。

示例語句：

BYS@bys1>alter table dept2 add constraint  valid_deptno check (deptno<5000);

BYS@bys1>select  constraint_name,table_name,constraint_type,status,deferrable,deferred,validated  from user_constraints;
CONSTRAINT_NAME                TABLE_NAME C STATUS   DEFERRABLE     DEFERRED  VALIDATED
------------------------------ ---------- - -------- -------------- --------- -------------
VALID_DEPTNO                   DEPT2      C ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

修改約束，關閉。

alter table scott.person disable novalidate constraint person_personid_pk;

開啟

alter table scott.person enable novalidate constraint person_personid_pk;

4.建表並定義約束的之表級定義與列級定義

概念:表級定義 列級定義

列級定義是在定義列的同時定義約束。

如果在department表定義主鍵約束

create  table department4(dept_id number(12) constraint pk_department primary key,

name varchar2(12),loc varchar2(12));

 表級定義:表級定義是指在定義了所有列後，再定義約束。這裡需要注意：not null約束只能在列級上定義。

以在建立employee2表時定義主鍵約束和外來鍵約束為例：

create  table employee2(emp_id number(4), name varchar2(15),dept_idnumber(2), constraint pk_employee primary key (emp_id),

constraint  fk_department  foreign key (dept_id)  references department4(dept_id));

5.建立表並使用約束示例

SQL>create table goods(goodsId char(8) primary key, --主鍵，也可以指定主鍵名。不指定則系統自動命名。

           goodsName varchar2(30)，

           unitprice number(10,2) check(unitprice>0),

           category varchar2(8), provider varchar2(30) );

SQL>create table customer( customerId char(8) primary key, --主鍵

           name varchar2(50) not null,  --不為空

           address varchar2(50),

           email varchar2(50) unique,

           sex char(2) default  '男'  check(sex in ('男'，'女'))， -- 一個char能存半個漢字，兩位char能存一個漢字

           cardId char(18) );

SQL>create table purchase( customerId char(8) references customer(customerId),

           goodsId char(8) references goods(goodsId),nums number(10)check (nums between 1 and 30));