oracle——05資料完整性

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

 約束
約束用以確保資料庫資料滿足特地的商業規則。在Oracle中，約束包括：

not null ：非空
如果在列上定義了not null，那麼插入資料時必須為該列提供資料，否則插不進去。

unique ：唯一
當定義了唯一約束以後，該列值是不能重複的，但是可以為null

primary key ：主鍵
用於唯一的標識錶行的資料，當定義主鍵約束後，該列不但不能重複而且不能為null

說明：一張表最多隻能有一個主鍵，但是可以有多個unique約束。
聯合主鍵：多列聯合起來作為主鍵。

foreign key ：外來鍵
用於定義主表和從表之間的關係，外來鍵約束要定義在從表上，主表則必須有主鍵約束或unique約束，當定義外來鍵約束後，要求外來鍵列資料必須在主表的主鍵列存在或是為null

check ：檢查
用於強制行資料必須滿足的條件，假定在sal列上定義了check約束，並要求sal列值在1000-2000之間，如果不在此區間內就提示錯誤。

 

案例分析
--建立商品表
create table goods(
goodsId char(8) primary key,--主鍵
goodsName varchar2(30),
unitPrice number(10,2) check(unitPrice>0),--單價大於0
category varchar2(8),
provider varchar2(30) );

 

--建立客戶表
create table customer(
customerId char(8) primary key, -- 主鍵
name varchar2(50) not null,
address varchar2(50),
email varchar(50) unique --email不能重複
sex char(2) default '男' check (sex in('男','女')),
cardId char(18) );

 

--購買purchase
create table purchase(
customerId char(8) reference customer(customerId), --外來鍵
goodsId char(8) reference goods(goodsId), --外來鍵
nums number(100) check(nums between 1 and 30) –數量

);

 如果建表時忘記建立必要的約束，則可以在建表後使用alter table命令為表增加約束。但是要注意：增加not null約束時，需要使用modify選項，而增加其他4種約束使用add選項。

--增加商品名也不能為空
alter table goods modify goodsName not null;

--增加身份證也不能重複
alter table customer add constraint cardUnique unique cardId;

--增加客戶的住址只能是"海淀","朝陽","東城","西城","通州","崇文"
alter table customer add constraint addresschek check(address in (''海淀,'朝陽','東城','西城','通州','崇文'));

 
刪除約束
alter table 表明 drop constraint 約束名

特別說明：
在刪除主鍵約束的時候可能有錯誤，比如：
alter table xxx drop primary key;
這是因為如果2張表之間存在主從關係，那麼在刪除主表的主鍵約束時，必須帶上cascade選項，如：
alter table xxx drop primary key cascade;

 

顯示約束資訊
1)顯示約束資訊
通過查詢資料字典檢視user_constraints，可以顯示當前使用者的所有約束資訊。
select constraint_name,constraint_type,status,validated from user_constraint where table_name='表名';

2)顯示約束列
通過查詢資料字典檢視user_cons_columns，可以顯示約束所對應的表的列的資訊。

select column_name,position from user_cons_columns where constraint_name='約束名';
3)更容易的辦法，直接用pl/sql developer檢視即可。

 

列級定義
列級定義是指在定義列的同時定義定義約束。
如在department表定義主鍵約束
create table department(
deptId number(4) constraint pk_department primary key,--主鍵
name varchar2(8),
loc varchar2(20)
);

說明：在列級定義時，加不加constraint pk_department都是可以的，區別是不加的話，系統會自動分配一個主鍵約束名稱，可讀性差。

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

create table employee2(
empId number(4),
name varchar2(20),
loc varchar2(40),
constraint pk_employee primary key(empId),
constraint fk_department foreign key(deptId) references department(deptId)
);

 

oracle資料完整性參照完整性域完整性 
 資料的完整性就是指資料庫中的資料在邏輯上的一致性和準確性。資料完整性一般分為3種：域完整性、實體完整性和參照完整性。完整性約束是通過限制列資料、行資料和表之間的資料來保證資料的完整性。完整性約束定義在表上，儲存在資料字典中。
 
1、域完整性
域完整性又稱列完整性，是指確定一個資料集對某一列是否有效和確定是否允許空值。域完整性通常是通過使用有效性檢查來實現的，還可以通過限制資料型別、格式或者可能的取值範圍來實現。
Oracle可以通過check約束實現域完整性。check約束表示一個欄位的輸入必須滿足check約束的條件，若不滿足，則資料無法正常輸入。
利用SQL語句操作check約束主要有如下幾種方式：
（1）利用SQL語句在建立表時建立check約束
 
Sql程式碼 
create table tableName(  
    columnName dataType [not null | null] [default expression] check(checkExpression),  
    columnName dataType [not null | null] [default expression] constraint checkName check(checkExpression),  
    ...n  
)  
 
 
示例程式碼：
 Sql程式碼 
create table person(  
    id int primary key,   
    sex varchar(4) check(sex='男' or sex='女'),  
    age int default 0 constraint ageCheck check(age>=0 and age<=125)  
)  
 
 
（2）利用SQL語句在修改時建立check約束
 Sql程式碼 
alter table tableName add(constraint checkName check(checkExpression), columnName dataType check(checkExpression))  
 
 
示例程式碼：
 Sql程式碼 
alter table person add(constraint sexCheck check(sex='男' or sex='女'), age int check(age>=0 and age<=125))  
 
 （3）利用SQL語句刪除check約束
 Sql程式碼 
alter table tableName drop constraint  checkName;  
 
 
示例程式碼：
 Sql程式碼 
alter table person drop constraint sexCheck;  
 
 
 
2、實體完整性
實體完整性又稱行完整性，它有求表中的每一行都有一個唯一的識別符號，這個識別符號就是主關鍵字。通過索引、unique約束和primary key約束可以實現實體完整性。
為表建立主鍵可實現表的實體完整性，主鍵可以是一列或幾列的組合，一個表只能有一個primary key約束，且primary key約束中的列不能為空值，當為表建立primary key時，Oracle會自動為primary key對應的列建立唯一索引，實現資料的唯一性。有了索引後，就可以實現對資料的快速訪問。
如果要確保一個表中的非主鍵列的值不可重複，可以在該列加上unique約束，unique約束也會自動產生索引。
primary key約束和unique約束的區別如下：
（1）一個資料表只能有一個primary key約束，但可以有多個unique約束。
（2）primary key對應欄位的值不能為null，而unique約束對應列的值可以為null。
（3）primary key對應欄位或欄位的組合的值是不能重複的，而unique約束列的值為null的時候是可以重複的，也就是說unique約束列可以有多個null值。
利用SQL建立和刪除primary key約束和unique約束

（1）在建立表的時候建立對應的約束
 Sql程式碼 
create table tableName(columnName dataType primary key, columnName dataType unique,...n);  
create table tableName(columnName1 dataType, columnName2 dataType, ...n, primary key(columnName1, columnName2), unique(columnName3, columnName4));  
 
 
示例程式碼：
 Sql程式碼 
create table person(id int primary key, name varchar(20) unique, age int);  
create table person(id int, name varchar(20), age int, primary key(id), unique(name));  
 
 （2）在修改表的時候建立對應的約束
 Sql程式碼 
alter table tableName add(constraint pkName primary key(column1, column2), constraint uniqueName unique(column3, column4));  
 
示例程式碼：
 Sql程式碼 
alter table person add(constraint personPk primary key(id), constraint nameUnique unique(name));  
 
（3）刪除primary key和unique約束
 Sql程式碼 
alter table tableName drop constraint constraintName;  
 
示例程式碼：
 Sql程式碼 
alter table person drop constraint personPk;  
       

 
3、參照完整性
參照完整性又稱為引用完整性，它可以保證從表（參照表）中的資料與主表（被參照表）中的資料的一致性。參照完整性通過定義從表的外來鍵與主表的主鍵相對應來實現。可以利用foreign key定義從表中的外來鍵，利用primary key定義主表中的主鍵。

--如果主表中的一條記錄被從表中的外來鍵所引用，那麼主表的這一條記錄既不能被刪除也不能修改主關鍵字。

--如果從表的外來鍵沒有進行not null約束，那麼從表的外來鍵也是可以為null的。
利用SQL語句定義和刪除表間的參照關係
（1）建立表時同時定義表間的參照關係
 
Sql程式碼 
create table tableName(columnName dataType [foreign key] references referencesTableName(referencesColumn),...n);  
 
--referencesTableName表示參照表的表名，referencesColumn表示參照的參照表中的欄位名，即參照的是參照表中的哪個欄位。
示例程式碼：
 
Sql程式碼 
create table saleBill(id int primary key, saleBookBillId int references saleBookBill(id));  
 
--該程式碼表示saleBill表中的saleBookBillId欄位是參照saleBookBill表的id欄位。
（2）通過修改表定義外來鍵約束
 
Sql程式碼 
alter table tableName add(constraint constraintName foreign key(column1[, column2, ...n]) references refTableName(refColumn1[, refColumn2, ...n]));  
 
示例程式碼：
 Sql程式碼 
alter table saleBill add(constraint saleBillFk foreign key(saleBookBillId) references saleBookBIll(id));  
 
（3）利用SQL語句刪除表間的參照關係
 Sql程式碼 
alter table tableName drop constraint constraintName;  
 
示例程式碼：
 Sql程式碼 
alter table saleBill drop constraint saleBillFk;