數據庫完整性

• 數據的準確性:for:學生的學號必須唯一
• 數據的相容性:是指數據庫同一對象在不同關系表中的數據是符合邏輯的
  • for: 生所選的課程必須是學校開設的課程
• 數據的完整性和安全性是兩個不同概念
  • 完整性是防止合法用戶誤操作
  • 安全性是防止非法用戶非法數據

• 如何保護數據庫的完整性:
  • 提供定義完整性約束條件(完整性規則)的機制
    • DDL 語句來定義,由DBMS 將其存入數據字典
  • 提供完整性檢查機制:一般在數據操縱之后
  • 違約處理
    • 拒絕（NO ACTION）執行該操作
    • 級連（CASCADE）執行其他操作

• 由DBMS進行完整性檢查

---

實體完整性

CREATE TABLE中用PRIMARY KEY定義(主碼定義)

• 插入或對主碼列進行更新操作時，DBMS按照實體完整性規則自動進行檢查。
  • 檢查主碼值是否唯一
  • 檢查主碼的各個屬性是否為空
  • 為避免對基本表進行全表掃描，RDBMS核心一 般都在主碼上自動建立一個索引
    • 例子:B+樹
• 單屬性構成的碼
  • 定義為列級約束條件
  • 定義為表級約束條件

• 多個屬性構成的碼
  • 定義為表級約束條件

[例5.1] 將Student表中的Sno屬性定義為碼

（1）在列級定義主碼

CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20) );

（2）在表級定義主碼

CREATE TABLE Student(Sno CHAR(9),Sname CHAR(20) NOT NULL,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20),PRIMARY KEY (Sno) );

[例5.2] 將SC表中的Sno，Cno屬性組定義為碼

CREATE TABLE SC(Sno CHAR(9) NOT NULL,Cno CHAR(4) NOT NULL,Grade SMALLINT,PRIMARY KEY (Sno,Cno)/*只能在表級定義主碼*/ );

參照完整性

若屬性（或屬性組）F是基本關系R的外碼它與基本關系S的 主碼

相對應（基本關系R和S不一定是不同的關系），則 對于R中每個元組在F上的值必須為：

• 或者取空值（F的每個屬性值均為空值）
• 或者等于S中某個元組的主碼值

例：學生關系的“專業號”是外碼，它參照專業關系的主碼“專業號”

學生關系中每個元組的“專業號”屬性只取兩類值：
（1）空值，表示該學生尚未確定專業
（2）非空值， 這時該值必須是專業關系中某個元組的“專業號”值，表 示該學生不可能屬于一個不存在的專業

SQL定義:

• 在CREATE TABLE中用FOREIGN KEY?短語定義哪些列為外碼
• 用REFERENCES短語指明這些外碼參照哪些表的主碼

例5.3：Student表的Sdept屬性是外碼，參照DEPT表的主碼Deptno

CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY,/* 列級完整性約束條件,Sno是主碼*/Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALINT,Sdept CHAR(20) FOREIGN KEY REFERENCES DEPT(Deptno)/*在列級定義參照完整性*/ );

例：Student表的Sdept屬性是外碼，參照DEPT表的主碼Deptno

CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY,/* 列級完整性約束條件,Sno是主碼*/Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20),FOREIGN KEY(Sdept) REFERENCES DEPT(Deptno)/*在表級定義參照完整性*/ );

例：關系SC中（Sno，Cno）是主碼。Sno，Cno分別參照Student表的主 碼和Course表的主碼

CREATE TABLE SC ( Sno CHAR(9) NOT NULL,Cno CHAR(4) NOT NULL,Grade SMALLINT,PRIMARY KEY (Sno, Cno),/*在表級定義實體完整性*/FOREIGN KEY (Sno) REFERENCES Student(Sno),/*在表級定義參照完整性*/FOREIGN KEY (Cno) REFERENCES Course(Cno)/*在表級定義參照完整性*/ );

破壞參照完整性的四種情況

• SC表中增加一個元組，該元組的Sno屬性的值在表Student 中找不到一個元組，其Sno屬性的值與之相等

• 修改SC表中的一個元組,修改后該元組的Sno屬性的值在 表Student中找不到一個元組，其Sno屬性的值與之相等

• 從Student表中刪除一個元組，造成SC表中某些元組的 Sno屬性的值在表Student中找不到一個元組，

• 修改Student表中一個元組的Sno屬性，造成SC表中某些 元組的Sno屬性的值在Student中找不到一個元組

違約處理:

• 拒絕（NO ACTION）執行
• 級聯（CASCADE）操作:

• 設置為空值（SET-NULL）:

[例5.4] 顯式說明參照完整性的違約處理示例

CREATE TABLE SC ( Sno CHAR(9) NOT NULL，Cno CHAR(4) NOT NULL，Grade SMALLINT,PRIMARY KEY(Sno,Cno)，FOREIGN KEY (Sno) REFERENCES Student(Sno)ON DELETE CASCADE/*級聯刪除SC表中相應的元組*/ON UPDATE CASCADE,/*級聯更新SC表中相應的元組*/FOREIGN KEY (Cno) REFERENCES Course(Cno)ON DELETE NO ACTION/*當刪除course 表中的元組造成了與SC表不一致時拒絕刪除*/ON UPDATE CASCADE/*當更新course表中的cno時，級聯更新SC表中相應的元組*/ );

用戶定義的完整性

針對某一具體應用的數據必須滿足 的語義要求

• 列值非空（NOT NULL）
• 列值唯一（UNIQUE）
• 檢查列值是否滿足一個條件表達式（CHECK）

[例5.5] 在定義SC表時，說明Sno、Cno、Grade屬性不允 許取空值。

CREATE TABLE SC ( Sno CHAR(9) NOT NULL,//Cno CHAR(4) NOT NULL,//可以不寫,primary key 已經約束了Grade SMALLINT NOT NULL,PRIMARY KEY (Sno, Cno), );

[例5.6]建立專業表DEPT，要求專業名稱Dname列取值唯一且不能取 空值，專業編號Deptno列為主碼。

CREATE TABLE DEPT ( Deptno NUMERIC(2),Dname CHAR(9) UNIQUE NOT NULL，/*要求Dname列值唯一, 并且不能取空值*/PRIMARY KEY (Deptno) );

[例5.7] Student表的Ssex只允許取“男”或“女”。

CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY,Sname CHAR(8) NOT NULL,Ssex CHAR(2) CHECK （Ssex IN （‘男’,’女’）），/*性別屬性Ssex只允許取'男'或'女' */Sage SMALLINT,Sdept CHAR(20) );

[例5.8] SC表的Grade的值應該在0和100之間。

CREATE TABLE SC ( Sno CHAR(9) ,Cno CHAR(4),Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)，/*Grade取值范圍是0到100*/PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno) );

[例5.9]當學生的性別是男時，其名字不能以Ms.打頭。

CREATE TABLE Student ( Sno CHAR(9),Sname CHAR(8) NOT NULL，Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20),PRIMARY KEY (Sno),CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')/*定義了元組中Sname和 Ssex兩個屬性值之間的約束條件*/ );

完整性約束命名子句

不命名的話就無法對完整性約束就行修改和刪除

CONSTRAINT <完整性約束條件名><完整性約束條件>

• <完整性約束條件>包括NOT NULL、UNIQUE、 PRIMARY KEY短語、FOREIGN KEY短語、CHECK 短語等

[例5.10]建立學生登記表Student，要求學號在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。

CREATE TABLE Student ( Sno NUMERIC(6)CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),Sname CHAR(20) CONSTRAINT C2 NOT NULL,Sage NUMERIC(3) CONSTRAINT C3 CHECK (Sage < 30),Ssex CHAR(2) CONSTRAINT C4 CHECK (Ssex IN ( ‘男’,'女')),CONSTRAINT StudentKey PRIMARY KEY(Sno) );//c1,c2,cc3,c4是完整性約束條件名

[例5.11]建立教師表TEACHER，要求每個教師的應發工資不低于3000 元。應發工資是工資列Sal與扣除項Deduct之和。

CREATE TABLE TEACHER ( Eno NUMERIC(4) PRIMARY KEY,/*在列級定義主碼*/Ename CHAR(10),Job CHAR(8),Sal NUMERIC(7,2),Deduct NUMERIC(7,2),Deptno NUMERIC(2),CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno),CONSTRAINT C1 CHECK (Sal + Deduct >= 3000) );

• 使用ALTER TABLE語句修改表中的完整性限制

[例5.12]去掉例5.10 Student表中對性別的限制。

ALTER TABLE Student DROP CONSTRAINT C4;

[例5.13] 修改表Student中的約束條件，要求學號改為在 900000~999999之間，年齡由小于30改為小于40

ALTER TABLE Student DROP CONSTRAINT C1;ALTER TABLE Student ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000AND 999999),ALTER TABLE Student DROP CONSTRAINT C3;ALTER TABLE Student ADD CONSTRAINT C3 CHECK(Sage < 40);

斷言

使用 CREATE ASSERTION語句，通過聲 明性斷言來指定更具一般性的約束。

CREATE ASSERTION<斷言名><CHECK 子句>

創建斷言

[例5.18] 限制數據庫課程最多60名學生選修

CREATE ASSERTION ASSE_SC_DB_NUM CHECK (60 >= (select count(*)From Course,SCWhere SC.Cno=Course.Cno andCourse.Cname ='數據庫') );

[例5.19]限制每一門課程最多60名學生選修

CREATE ASSERTION ASSE_SC_CNUM1 CHECK (60 >= ALL (SELECT count(*)FROM SCGROUP by cno) );

[例5.20]限制每個學期每一門課程最多60名學生選修

//首先需要修改SC表的模式，增加一個“學期（TERM）”屬性 ALTER TABLE SC ADD TERM DATE; //定義斷言： CREATE ASSERTION ASSE_SC_CNUM2 CHECK (60 >= ALL (SELECT count(*) FROM SCGROUP by cno,TERM) );

刪除斷言

DROP ASSERTION <斷言名>;

---

觸發器

得到再補上吧

Trigger,是用戶定義在關系表上的一類由事件驅動的特殊過程

• 行級觸發器（FOR EACH ROW）
• 語句級觸發器（FOR EACH STATEMENT）

定義觸發器

CREATE TRIGGER <觸發器名> {BEFORE | AFTER} <觸發事件> ON <表名> REFERENCING NEW|OLD ROW AS <變量> FOR EACH {ROW | STATEMENT} [WHEN <觸發條件>]<觸發動作體>

[例5.21]當對表SC的Grade屬性進行修改時，若分數增加了10%則將此 次操作記錄到下面表中：

SC_U（Sno,Cno,Oldgrade,Newgrade）

CREATE TRIGGER SC_T AFTER UPDATE OF Grade ON SC REFERENCINGOLD row AS OldTuple,NEW row AS NewTuple FOR EACH ROW WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade) INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

[例5.22] 將每次對表Student的插入操作所增加的學生個數 記錄到表StudentInsertLog中。

CREATE TRIGGER Student_Count AFTER INSERT ON Student REFERENCINGNEW TABLE AS DELTA FOR EACH STATEMENTINSERT INTO StudentInsertLog (Numbers)SELECT COUNT(*) FROM DELTA

[例5.23] 定義一個BEFORE行級觸發器，為教師表Teacher 定義完整性規則“教授的工資不得低于4000元，如果低 于4000元，自動改為4000元”

CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON Teacher FOR EACH ROWBEGINIF (new.Job='教授') AND (new.Sal < 4000)THEN new.Sal :=4000;END IF;END;

###刪除觸發器

DROP TRIGGER <觸發器名> ON <表名

總結

以上是生活随笔為你收集整理的f分布表完整图a=0.01_SQL数据库完整性的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。