資料庫複習3——資料庫完整性，複習資料庫完整性

資料庫複習3——資料庫完整性，複習資料庫完整性
資料庫複習CH5 完整性5.1 完整性條件約束

資料庫完整性是指資料庫中資料在邏輯上的一致性、正確性、有效性和相容性，那麼完整性條件約束就是指使用者插入、修改和刪除操作時，DBMS為了保證資料庫邏輯上的一致性、正確性、有效性和相容性所必需要檢查的約束條件

C.J.Date在《An Introduction to Database Systems》一書中描述了四種完整性條件約束：

• 類型約束
• 屬性約束
• 關係變數約束
• 資料庫約束

C.J.Date提出的理論多半是建立在他自創的Tutorial D這個概念型資料庫操作語言上的，下面的描述也是基於Tutorial D，注意不要和SQL混淆

（1）類型約束

類型約束是關於自訂類型的種類（或就是數域）以及值大小的約束，例如我們自定類型weight是實數類型且要求weight必須大於0，那麼用Tutorial D定義一個類型約束如下：

TYPE weight POSSREP(RATIONAL) CONSTRAINT the_weight(weight) > 0.0;

POSSREP是possible representation（可能的表達方式），RATIONAL是有理數，the_加weight（前面提到過這是C.J.Date提出的對類型的操作符號，表示取值）weight作用於weight表示取值

（2）屬性約束

屬性約束就是定義關係時定義屬性的類型產生的隱式約束，Tutorial D定義一個關係如下：

VAR S BASE RELATION(S# S#, status integer, city char);

比如上例的status就需要隱式遵守類型integer的類型約束

（3）關係變數約束

關係變數約束是對關係中元組的約束，如限定在倫敦的供應商狀態一定是20：

Constraint sc1 is_empty(S where city = 'London' and status ~= 20);

關係變數約束總是立即檢查的

（4）資料庫約束

關係變數約束是針對單個關係變數內部屬性的約束（可以把關係變數就理解為關係/表），若約束涉及多個關係變數，則稱之為資料庫約束

如下的資料庫約束，限定零件商的個數必須等於供應表中零件商的個數：

Constraint dbc1 count(SP(p#)) = count(P(p#));

（5）約束設計的黃金準則（Golden Rule）

關於Golden Rule，C.J.Date在這裡想要表達的是我們定義的所有完整性條件約束都稱之為內部約束，使用者和DBMS都很清楚，比如體重必須大於0

然而我們設計的系統還有很多其他的外部約束，比如說我的體重是69.5，那麼可以插入一個元組(jcguo, 69.5)，但是插入(jcguo, 100.5)說我是個大胖子DBMS也是允許的，但這違反了現實世界的約束

這屆涉及到之前在完整性定義中包括的正確性，C.J.Date認為這部分的完整性應該由使用者或者說DBA在資料庫外部限定，而不是交給DBMS

5.2 鍵Key

資料庫中可以定義一種特殊的完整性條件約束——鍵（key，或者有時也翻譯為碼，我叫鍵比較順口）

（1）各種鍵的定義

我們先來弄清楚各種鍵的定義：

• super key：超鍵，在關係中能夠唯一標識元組的屬性集
• candidate key：候選索引鍵（一般叫候選碼），不含有多餘屬性/不可規約的超鍵
• primary key：主鍵，使用者選做唯一表識元組的候選索引鍵，主鍵不能為null
• alternate key：可選鍵，候選索引鍵中除了主鍵以外，沒有被使用者選中的候選索引鍵
• foreign key：外鍵，是依賴於其他關係用於保證資料庫邏輯完整性的約束

前四個key都很好理解，下面著重理解一下外鍵

（2）外鍵

關係R1聲明在屬性a1上建立外鍵FK，那麼有：

• 這個外鍵FK必須reference（引用or以來）於另一個關係R2的候選索引鍵CK（一般reference另一個關係的主鍵，設CK建立於屬性a2上）
• 這個reference的作用就是保證R1中屬性a1中出現的值全部都在R2的a2中出現（完整性條件約束）

注意，外鍵FK並不要求R2中屬性a2（CK）中出現的值都必須在R1中屬性a1中出現，只是說單方面的約束：一旦在FK中出現則必須在CK中出現

外鍵還有另一個最重要的特性：referential action（關聯動作），關聯動作是一個隱式的特殊的觸發器（每當滿足出發條件時都會出發的動作集合）

下面在講SQL完整性時會詳細闡述SQL所支援的關聯動作

5.3 SQL完整性

再次強調之前我們複習的所有完整性都是抽象性的概念，需要理解，那麼這一小節SQL完整性則需要記憶了

（1）域約束

域約束（Domain Constraints）是SQL中對屬性取值範圍的約束，SQL聲明域約束是在create table時完成的，支援帶名稱的顯式約束聲明以及不帶名稱的便捷約束聲明兩種方式：

create table Student(    S# char(10),    name char(20),    gender char(1),    constraint gc check (gender in ('F', 'M'))  );

和下面等價：

create table Student(    S# char(10),    name char(20),    gender char(1) check (gender in ('F', 'M')));

check從句中可以使用常用的比較關係操作符號，也可以使用in構造複合陳述式

給約束命名是為了某些特殊情況我們需要對約束進行修改，見我另外一篇博文：

另外SQL支援對已聲明的table添加約束，使用alter table語句，本課程暫不要求

（2）主鍵

SQL主鍵聲明也是在create table時聲明的，聲明主鍵也有兩種方式（顯式和便捷式，顯式主鍵不需要名字，因為一個關係/表只能有一個主鍵）：

create table Student(    S# char(10),    name char(20),    gender char(1) check (gender in ('F', 'M')),    primary key (S#));

等價於：

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')));

需要注意的是主鍵不可為空，不能插入一條不包含主鍵/主鍵為null的元組

primary key也可以作用於多個屬性，如同前面概念講解中所提到的，如何設計依需求而定

（3）unique

SQL中實現候選碼使用unique約束，一個關係可以有多條unique約束

這句話翻譯自老師ppt，個人覺得需要補充：

• SQL中淡化了candidate key的概念
• unique約束不僅可以實現候選碼，也可以實現超碼，也就是它沒有不可約的限制

聲明uniqle約束和主鍵類似，舉例略

（4）外鍵

Foreign key之前留了個坑，這裡直接舉例吧：

create table Dept(    D# char(3) primary key,    name char(20),    type char(20) check (type in ('engineering', 'science', 'business')));

先建立了一個table Dept是系的關係，它的主鍵是D#，再建立一個Student表，它的屬性D#上聲明了一個引用Dept(D#)的外鍵：

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3) foreign key references Dept(D#));

上面的聲明等價於：

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3),    contraint dfk foreign key references Dept(D#));

這裡外鍵的約束就是限制學生表裡的D#必須在系表裡的D#中出現，那麼很容易想到一個問題：如果USTC新任校長萬老大把11系拆了，也就是系表裡把D#為’011’的元組刪除了的話，那我們11系的學生在Student表裡的tuples不就不滿足外鍵約束了嗎

這時候就需要引入關聯動作，關聯動作分為級聯（Cascade）動作和設定（Set）動作

1.級聯動作

級聯動作就是一系列的主鍵-外鍵-主鍵-外鍵-…約束引用鏈條的後端的某個主鍵delete或update，會產生（自動的，觸發器）沿引用鏈條從後往前的一系列外鍵的delete或update操作

例如我們聲明一個D#的刪除級聯，即：

create table Student(    S# char(10) primary key,    name char(20),    gender char(1) check (gender in ('F', 'M')),    D# char(3) foreign key references Dept(D#) on delete cascade);

此時刪除11系，那麼根據級聯動作定義，所有11系的學生都被刪除，因為Student表通過外鍵引用了Dept表的主鍵

再假設Dept表引用了一個學校編號U#的外鍵，它是一個University表的主鍵，此時構成了一個引用鏈條，若這個University表刪除了USTC的條目，那麼根據級聯動作定義USTC的11系，11系的學生都將被自動刪除

級聯動作也可以是on update cascade，比如11系改系別號為1系了，那麼11系的學生能夠自動的把D#改成‘001’

DBMS處理級聯動作是按事務來處理的，若在引用鏈條中某一個部分的級聯動作違反了其他的完整性條件約束，那麼整個修改動作都會被復原（事務的概念），即修改失敗

ppt中寫道：

Referential integrity is only checked at the end of a transaction
–– Intermediate steps are allowed to violate referential integrity provided later steps remove the violation, otherwise it would be impossible to create some database states, e.g. insert two tuples whose foreign keys point to each other

很不幸的是在實驗中我發現這條規則MySQL適用但Oracle並不適用（至少在PL/SQL中）

SQL支援一個外鍵同時有一個刪除級聯和一個更新級聯

2.設定動作

設定動作比較好理解：

• on delete set null
• on delete set default

5.4 斷言和觸發器（1）斷言

SQL還支援使用斷言（Assertion）在關係外部定義資料庫必須滿足的條件，文法為：

create assertion <assertion_name> check <predicate>;

Assertion有點類似於C.J.Date定義的資料庫約束，可以針對單表或多表

ppt中一個複雜的例子，限定每個部門的工資總和必須小於d1部門的工資總和：

create assertion sac check(    not exists(        select * from EMP e2         where (select sum(sal) from EMP e1 where e1.d# = e2.d#)             >= (select sum(sal) from EMP where d# = 'd1')    ));

PS：我咋覺得第一個where後面要加d# ~= 'd1' and呢？

（2）觸發器

觸發器定義了資料庫狀態改變（元組修改）時需要自動執行的一系列動作，定義觸發器包括兩個要點：

• 觸發條件
• 執行動作

觸發器還有幾個重要屬性：

• 事前觸發或事後觸發
• 行觸發還是整體觸發

事前觸發或事後觸發比較好理解，行觸發還是整體觸發是指的是一條update或delete時若涉及多個元組的修改，是整體觸發一次還是每個元組都觸發一次

還是舉例說明，下面是我在Oracle上建立的行觸發器：

create or replace trigger countStudafter delete or insert or update on Studfor each rowbegin      update Dept set S_count =  S_count+1 where D# = :new.D#;      update Dept set S_count =  S_count-1 where D# = :old.D#;end;

這是一個用來統計系裡人數的觸發器（或者說叫做保證系裡人數完整性的觸發器），分點說明：

• after delete or insert or update on Stud指定了觸發器的條件和觸發時機：當Stud表有刪除、插入或更新操作之後觸發
• for each row聲明這是一個行觸發器，按行觸發
• begin和end包體裡指定了觸發器的動作

至於其中的:new和是:old保留變數，對應刪除、插入或更新三個操作他們有如下含義：

含義	insert	update	delete
:new	新插入的元組	更新後的元組	null
:old	null	更新前的元組	刪除的元組