1Z0-051-DDL-表的約束

表的約束

表約束是數據庫能夠實施業務規則以及保證數據遵循實體-關系模型的一種手段。如果

DML違法了約束，則將自動回滾整個語句。

1.1 約束類型

（1）UNIQUE 約束

注：對于具有UNIQUE約束的列，可以插入多個包含NULL的行，而對于PRIMARYKEY約束而言，不能存在這種可能。

（2）NOT NULL約束

（3）PRIMARY KEY 約束

注：UNIQUE和PRIMARY KEY 約束需要索引，如果不存在，就會自動予以創建。一張表只有一個主鍵。

（4）CHECK 約束

（5）FOREIGN KEY約束

注：外鍵約束在子表上定義，但此時在父表上必須存在UNIQUE或primary key約束。一般而言，unique約束所有列以及foreign key 約束中的所有列最好也定義not  null約束。

【問題】在字表中插入父表中沒有匹配行的行，將發生錯誤，同樣在父表中將在子表中已經存在的行刪除，則刪除相應的行將引發錯誤。

【解決方案1】在創建約束是創建為ON DELETE CASCADE。

  這意味著，如果刪除父表中的行，那么Oracle將在子表中搜索索引匹配行，并刪除它們。這將自動發生。

【解決方案2】（此方案較溫和）將約束創建為ONDELETE SET NULL.

在這種情況下，如果刪除父表中的行，Oracle將在子表中搜索索引匹配行,并將外鍵列設為空。

1.2 定義約束

【案例】在創建表時定義約束

 create table dept(

  2 deptno number(2,0) not null constraint dept_deptno_pk primary key,

  3 constraint dept_deptno_ck check (deptno between 10 and 90),

  4 dname varchar2(20) constraint dept_dname_nn not null);

Table created.

SQL> alter tableemp rename to emp1;

Table altered.

SQL> create tableemp(

  2 empno number(4,0) not null constraint emp_empno_pk primary key,

  3 ename varchar2(20) constraint emp_ename_nn not null,

  4  mgrnumber(4,0) constraint emp_mgr_fk references emp(empno),

  5  dobdate,

  6 hiredate date,

  7 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)

  8  ondelete set null,

  9 email varchar2(30) constraint emp_email_uk unique,

 10 constraint emp_hiredate_ck check(hiredate >=dob +365*16),

 11 constraint emp_email_ck

 12 check((instr(email,'@') > 0) and (instr(email,'.') > 0)));

Table created.

1.3 約束狀態

任何時候，約束都處于啟用或禁用狀態，驗證或非驗證狀態。

• ENABLE     VALIDATE   無法輸入違反約束的行，而且表中的所有行都符合約束。（理想情況，默認）

• DISABLE     NOVALIDATE  可以輸入任何數據（無論是否符合要求），表中可能已經存在不符合要求的數據。（批量導入數據時）

• ENABLE  NOVALIDATE   表中可能已經存在不符合要求的數據，但現在輸入的所有數據必須符合要求。

•  DISABLE VALIDATE 這種情況不存在

SQL> alter tableemp modify constraint emp_ename_nn disable novalidate;

Table altered.

SQL> insert intoemp select * from emp1;

SQL> alter tableemp modify constraint emp_ename_nn enable novalidate;

Table altered.

SQL>update empset ename = 'NOT KNOWN' where ename is null;

SQL> alter tableemp modify constraint emp_ename_nn enable validate;

Table altered.

1.4 約束檢查

可以在執行語句是檢查約束（IMMEDIATE 約束）或者提交事務是檢查約束(DEFERRED約束）。默認情況下，所有約束都是IMMEDIATE約束，不能延遲。

將上例子中的代替方法將約束創建為DEFERRED（延遲）約束。

SQL> setconstraint emp_ename_nn deferred;

SQL> insert intoemp select * from emp1;

SQL>update empset ename = 'NOT KNOWN' where ename is null;

SQL>commit;

SQL>set constraint emp_ename_nn immediate;

要使約束成為延遲約束，必須使用適當方法進行創建。

SQL>alter table emp add constraint emp_ename_nn

 check(ename is not null) deferrable initiallyimmediate;

  2

Table altered.

此時重新執行約束延遲才能成功。

SQL> setconstraint nn_emp_ename deferred;

Constraint set.

SQL> insert intoemp select * from emp1;

SQL>update empset ename = 'NOT KNOWN' where ename is null;

SQL>commit;

SQL> setconstraint nn_emp_ename immediate;

Constraint set.

（1）查找約束的名稱

SQL> selectconstraint_name,constraint_type,column_name

  2  fromuser_constraints natural join user_cons_columns

  3 where table_name ='&Table';

（2）修改約束名稱

Altertable  emp rename constraint old_name tonew_name;

（3）向webstore模式中添加如下約束

SQL>alter table orders add constraint pk_order_id primary key(order_id);

SQL>alter table products add constraint pk_product_id primarykey(product_id);

SQL>alter table order_items add constraint fk_product_id foreignkey(product_id) references products(product_id);

SQL>alter table order_items add constraint fk_order_id foreign key(order_id) references orders(order_id);

SQL>alter table orders add constraint fk_customer_id foreignkey(customer_id) references customers(customer_id);