Oracle 學習筆記 10

本次筆記來學習約束，約束在表中無處不在。例如，表中的資料不允許為空白或者是表中id為設為主鍵，都是約束。

約束分類：

        主鍵約束（PRIMARY KEY）：主鍵表示表中一個唯一的標識，本身不可為空。

        唯一約束(UNIQUE)：在一個表中只能允許建立一個主鍵約束，其它的列不會與此列產生重複的值。

        檢查約束(CHECK)：檢查一個列的內容是否合法，如：工資在 1500 – 3000之間。

        非空約束(NOT NULL)：內容不可為空。

        外鍵約束(FOREIGN KEY)：兩張表之間的進行約束操作。

約束也是有名字的，如果不指定約束的名字，系統會自動的為我們重建約束名，格式類似於sys_cn 。也可以自己創

建約束的名字，格式一般為：constraint  表名_列名_約束名 約束。之所以使用 “表名_列名_約束名”這樣的格式，

也是為了達到一種見名知意的效果。定義的方式：列約束必須跟在列的後面進行定義，表級約束可以不跟在列的後面，進行單獨的定義。

約束的範圍：資料行層級條件約束和表級約束，資料行層級條件約束只能作用在一個列上，表級約束可以作用在多個列上，也可以作用在一個列上。

可以建立約束，也可以修改約束。建立約束在建立表的時候建立，修改約束在表建立完成之後再進行修改。

基本的文法格式：

資料行層級條件約束定義：

CREATE TABLE table_name(

       列名1 列的資料類型類型  [約束 | constraint 約束名稱  約束],

  列名2 列的資料類型類型  [約束 | constraint 約束名稱  約束]

)

表級約束定義：

CREATE TABLE table_name(

         列名1  列的資料類型 ，

      列名2  列的資料類型 ，

      constraint  約束名稱 約束(列名)

)

NOT NULL 約束

非空的約束只能定義在列上。使用非空約束，表示一個欄位的內容不允許為空白，即：插入的資料必須是有內容

的，不允許為null 。

例：建立表emp2 ，表中包含三列，id ， name  ， salary 。其中要求id 列不可為空。

create table emp2(

       idnumber(9) constraintemp2_id_nn  not null ,

       namevarchar2(20) not null ,

       salarynumber(10 ,2 )

)

constraintemp2_id_nn 資料行層級條件約束。意思是為約束起一個名字。使用constraint關鍵字來進行定義。

例：資料測試，嘗試向emp2中添加name為null的資料

insert into emp2

values(1001 , null , 2000)

 

ORA-01400: 無法將 NULL 插入 ("SCOTT"."EMP2"."NAME")

設定非空約束之後，如果插入空值，則出現上述錯誤。已經指名了列名name無法插入空值。

唯一約束（UNIQUE）

列的值是唯一的，在表中的其它列中不允許有重複的值。但是允許表中出現多個null值。

例：建立表emp3

create table emp3

(

       idnumber(8) constraintemp3_id_uk unique ,

       namevarchar2(20) constraintemp3_name_nn not null ,

       salarynumber(10,2),

       emailvarchar2(20),

       --表級約束

       constraintemp3_email_uk unique(email)

)

執行下面的語句兩次之後：

insert into emp3

values(100 , 'zhangsan1' ,2000, 'zhang@128.com' )

 

ORA-00001: 違反唯一約束條件 (SCOTT.EMP3_ID_UK)，提示違反了唯一的約束。但是如果插入值得集合為（100 ， 'zhang' , 2000 , null）和(1001 ,'san' , 3000 , null)的兩條資料，雖然上面兩個值得集合中email列中都是空值，仍然可以成功插入，設定了唯一約束的列，null不認為是重複。

主鍵約束(PRIMARY KEY)

一般使用在表的id上面，本身預設內容不可為空。

create table emp4

(

       idnumber(8) constraintemp4_id_pk primary key  ,

       namevarchar2(20) constraintemp4_name_nn not null ,

       salarynumber(10,2),

       emailvarchar2(20),

      

       --表級約束

       constraintemp4_email_ukunique(email)

)

insert into emp4values(null , 'zhangsan' ,100 ,'125')

ORA-01400: 無法將 NULL 插入 ("SCOTT"."EMP4"."ID")

insert into emp4values(1001 , 'zhangsan' ,100 ,'125')

ORA-00001: 違反唯一約束條件 (SCOTT.EMP4_ID_PK)

當表中已經存在id 為1001 的記錄時，依然不能插入資料。設定了主鍵的約束，值不是不允許為空白和重複的。

外鍵約束（FOREIGN KEY）

外鍵約束是針對兩張表的約束。在字表中設定的外鍵，在主表中必須設定成主鍵。再刪除是，應該先刪除子表的記錄在刪除父表的記錄。

建立外鍵約束語句：

constraint 約束名稱  foreign key (子表列名) references 父表名(父表列名)。子表列名和父表列名應該相同。

create table emp8

(

       idnumber(8) ,

       name varchar2(20) constraintemp8_name_nn not null ,

       salary number(10,2) check (salary >1500 and salary < 30000),

       email varchar2(20),

       department_id number(10),

       --表級約束

       constraintemp8_email_uk unique(email) ,

       constraintemp8_id_pk primary key (id),

       constraintemp8_dept_id_fk  foreignkey (department_id) references  

       departments(department_id)

)

insert into emp6

values (1001 , 'aa' ,null , 2000 ,2000)

ORA-02291:違反完整約束條件 (SCOTT.EMP6_DEPT_ID_FK) -未找到父項關鍵字 。上面deptment_id = 2000 在deptments表中是不存在的，則是出現上述錯誤。

設定了外鍵約束之後，字表中的列受到父表的約束，如果是父表中沒有的值 ，那麼資料便不能插入成功。

 

–ON DELETE CASCADE(串聯刪除): 當父表中的列被刪除時，子表中相對應的列也被刪除 –ON DELETE SET NULL(級聯置空): 子表中相應的列置空

 

Check 約束

             使用check約束，檢查一個類中插入的內容是否合法。如工資在1500 - 5000 之間。

create table emp8

(

       id number(8) ,

       name varchar2(20) constraint emp8_name_nn not null ,

       salary number(10,2) check (salary >1500 and salary < 30000),

       email varchar2(20),

       department_id number(10),

       --表級約束

       constraint emp8_email_ukunique(email) ,

       constraint emp8_id_pkprimarykey (id),

       constraint emp8_dept_id_fk  foreign key (department_id)

       references departments(department_id)

)

SQL> insert into emp8

 2  values(1001 , 'zhangsan' , 2000 , 'aa' , 30) ;

 1 row inserted

 

SQL> insert into emp8

 2  values(1002 ,'zhang' , 500 , 'bb' , 30);

ORA-02290:違反檢查約束條件 (SCOTT.SYS_C005496)，當插入的資料不滿足salary的範圍便會出上述錯誤提示。

修改約束

修改約束

添加 not null 約束  要使用 modify

Alter table emp5

Modify(salary number(10) not  null )

刪除約束 

Alter table emp5

Drop constraint emp5_name_nn(約束名)

添加約束

Alter table emp5

Add constraint emp5_nameul unique(name);

 

有效化約束和無效化約束。

Alter table emp3

enable constraint emp3——name-uk （約束名）

 

Alter table emp3

Disable constraint emp3——name-uk （約束名）