十五、oracle 約束

標籤：

 

一、維護資料的完整性
資料的完整性用於確保資料庫資料遵從一定的商業和邏輯規則，在oracle中，資料完整性可以使用約束、觸發器、應用程式（過程、函數）三種方法來實現，在這三種方法中，因為約束易於維護，並且具有最好的效能，所以作為維護資料完整性的首選。

     

二、約束
約束用於確保資料庫資料滿足特定的商業規則。在oracle中，約束包括：not null、 unique， primary key， foreign key和check 五種。
1)、not null(非空)
如果在列上定義了not null，那麼當插入資料時，必須為列提供資料。
2)、unique(唯一)
當定義了唯一約束後，該列值是不能重複的，但是可以為null。
3)、primary key(主鍵)
用於唯一的標示表行的資料，當定義主鍵約束後，該列不但不能重複而且不能為null。
需要說明的是：一張表最多隻能有一個主鍵，但是可以有多個unqiue約束。
4)、foreign key(外鍵)
用於定義主表和從表之間的關係。外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique 約束，當定義外鍵約束後，要求外鍵列資料必須在主表的主鍵列存在或是為null。
5)、check
用於強制行資料必須滿足的條件，假定在sal列上定義了check約束，並要求sal列值在1000-2000之間如果不在1000-2000之間就會提示出錯。

    

三、商店售貨系統資料表設計案例一
現有一個商店的資料庫，記錄客戶及其購物情況，由下面三個表組成：
商品goods(商品號goodsId，商品名goodsName，單價unitprice，商品類別category，供應商provider)；
客戶customer(客戶號customerId，姓名name，地址address，電郵email，性別sex，身份證cardId)；
購買purchase(客戶號customerId，商品號goodsId，購買數量nums)；
請用SQL語言完成下列功能：
1. 建表，在定義中要求聲明：
(1). 每個表的主外鍵；
(2). 客戶的姓名不可為空值；
(3). 單價必須大於0，購買數量必須在1到30之間；
(4). 電郵不能夠重複；
(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)
);
表是預設建在SYSTEM資料表空間的

          

四、商店售貨系統資料表設計案例二
如果在建表時忘記建立必要的約束，則可以在建表後使用alter table命令為表增加約束。但是要注意：增加not null約束時，需要使用modify選項，而增加其它四種約束使用add選項。
1)、增加商品名也不可為空
SQL> alter table goods modify goodsName not null;
2)、增加身份證也不能重複
SQL> alter table customer add constraint xxxxxx unique(cardId);
3)、 增加客戶的住址只能是’海澱’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’；
SQL> alter table customer add constraint yyyyyy check (address in (‘海澱‘,‘朝陽‘,‘東城‘,‘西城‘,‘通州‘,‘崇文‘,‘昌平‘));

              
刪除約束
當不再需要某個約束時，可以刪除。
alter table 表名 drop constraint 約束名稱；
特別說明一下：在刪除主鍵約束的時候，可能有錯誤，比如：alter table 表名 drop primary key；這是因為如果在兩張表存在主從關係，那麼在刪除主表的主鍵約束時，必須帶上cascade選項 如像：alter table 表名 drop primary key cascade;
                   
顯示約束資訊
1)、顯示約束資訊
通過查詢資料字典視圖user_constraints，可以顯示目前使用者所有的約束的資訊。
select constraint_name, constraint_type, status, validated from user_constraints where table_name = ‘表名‘;
2)、顯示約束列
通過查詢資料字典視圖user_cons_columns，可以顯示約束所對應的表列資訊。
select column_name, position from user_cons_columns where constraint_name = ‘約束名‘;
3)、當然也有更容易的方法，直接用pl/sql developer查看即可。簡單示範一下下...

        

五、表級定義、列級定義
1)、列級定義
列級定義是在定義列的同時定義約束。
如果在department表定義主鍵約束

create table department4(
   dept_id number(12) constraint pk_department primary key,
   name varchar2(12), 
   loc varchar2(12)
);

     
2)、表級定義
表級定義是指在定義了所有列後，再定義約束。這裡需要注意：
not null約束只能在列級上定義。
以在建立employee2表時定義主鍵約束和外鍵約束為例：

create table employee2(
   emp_id number(4), 
   name varchar2(15),
   dept_id number(2), 
   constraint pk_employee primary key (emp_id),
   constraint fk_department foreign key (dept_id) references department4(dept_id)
);

十五、oracle 約束