oracle 11g 學習筆記 10_31(2)_維護資料的完整性

對於有海量資料的表，建立索引查詢速度比沒有建立索引的要快得多。
維護資料的完整性

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

約束用於確保資料庫資料滿足特定的商業規則，在oracle中，約束包括：not null、unique、primary key、foreign key和check五種。
.not null(非空)
如果在列定義了 not null，那麼當插入資料時，必須為列提供資料。
.unique（唯一）
當定義了唯一約束後，該列值是不能重複的，但是可以為null
.primary key(主鍵)
用於唯一的標示表行的資料，當定義主鍵約束後，該列不但不能重複而且不能null。
需要說明的是：一張表最多隻能有一個主鍵，但是可以有多個unqiue約束。
.foreign key(外鍵)
用於定義主表和從表之間的關係，外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當定義外鍵約束後，要求外鍵列資料必須在主鍵列存在或是為null
.check
用於強制行資料必須滿足的條件，假定在sal列上定義了check約束，並要求sal列值在1000-200之間，弱國不在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). 客戶的性別必須是 男 或者 女，預設是男； 

create table GOODS(  goodsid   CHAR(8) primary key,--設定主鍵  goodsname VARCHAR2(30),  unitprice NUMBER(10,2) check(unitprice > 0),  category  VARCHAR2(8),  provider  VARCHAR2(30));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));create table PURCHASE(  customerid CHAR(8) references customer(customerId),--指向customer表的customerId  goodsid    CHAR(8) references goods(goodsId),  nums       NUMBER(5) check(num between 1 and 30));

*增加約束
商店售貨系統資料表設計案例（2）?   
如果在建表時忘記建立必要的約束，則可以在建表後使用alter table命令為表增加約束。但是要注意：增加not null約束時，需要使用modify選項，而增加其它四種約束使用add選項。  
1. 增加商品名不可為空

alter table goods modify goodsName not null;

2. 增加身份證不能重複

alter talbe customer add constraint cardIdUnique unique(cardId);

其中，add constraint cardIdUnique 表示增加一個名為cardIdUnique的約束，constraint表示約束的意思。
3. 增加客戶的住址只能是'天河'、'越秀'、'海珠'、'番禺'、'荔灣'、'從化'

alter table customer add constraint addrCheck 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這個工具查看
二、表級定義和列級定義

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

create table PURCHASE(  customerid CHAR(8) references customer(customerId),--指向customer表的customerId  goodsid    CHAR(8) references goods(goodsId),  nums       NUMBER(5) check(num between 1 and 30));

*表級定義
表級定義是指在定義了所有列之後，再定義約束，需要注意的是：not null約束只能在列級上定義
例：

create table GOODS(  goodsid   CHAR(8),  goodsname VARCHAR2(30),  unitprice NUMBER(10,2),  category  VARCHAR2(8),  provider  VARCHAR2(30)，constraint pk_goodsid primary key (goodsId),--設定主鍵constraint chk_price check(unitprice > 0) --價格大於零);

這兩種定義實質上是沒有區別的，要是用列級定義，由於沒有給約束的一個名字，系統會隨機地給約束一個名字。這個就是區別。
三、索引

*介紹
索引適用於加速資料存取的資料對象，合理的使用索引可以大大降低i/o次數，從而提高資料訪問效能，索引有很多種：
為什麼添加索引後，會加快查詢速度？
*建立索引
.單列索引是基於單個列所建立的索引，比如
create index 索引名 on 表名(列名)
.複合索引
符合索引是基於兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同，比如

create index emp_idxl on emp(ename,job);create index emp_idxl on emp(job,ename);

*索引使用原則
1）在大表上建立索引才有意義
要是表的資料小，那麼就很沒有意義了，即浪費了我們做索引的時間，還佔用了空間。
2）在where子句或是串連條件上經常引用的列上建立索引
3）索引的層次不要超過4層

*索引缺點分析
1）建立索引，系統要佔用大約為表的1.2倍的硬碟和記憶體空間來儲存索引
2）更新資料的時候，系統必須要有額外的時間來同時對索引進行更新，以鑑效組資料和索引的一致性。
不恰當的索引不但無濟於事，反而會降低系統效能。因為大量的索引在進行插入、修改和刪除操作時比沒有索引花費更多的系統時間。