数据库完整性约束
完整性約束
????????? ——數(shù)據(jù)庫(kù)完整性約束簡(jiǎn)介
- 理解數(shù)據(jù)庫(kù)完整性約束的作用；
- 理解數(shù)據(jù)庫(kù)中的各種約束的作用。
?
- 完整性約束是保證用戶對(duì)數(shù)據(jù)庫(kù)所做的修改不會(huì)破壞數(shù)據(jù)的一致性，是保護(hù)數(shù)據(jù)正確性和相容性的一種手段.
?
- 維護(hù)完整性
- 在一個(gè)DBMS之中，為了能夠維護(hù)數(shù)據(jù)庫(kù)的完整性，必須能夠提供以下的幾種支持：
| 提供定義完整性約束條件機(jī)制 | 在數(shù)據(jù)表上定義規(guī)則，這些規(guī)則是數(shù)據(jù)庫(kù)中的數(shù)據(jù)必須滿足的語(yǔ)義約束條件； |
| 提供完整性檢查的方法 | 在更新數(shù)據(jù)庫(kù)時(shí)檢查更新數(shù)據(jù)是否滿足完整性約束條件； |
| 違約處理 | DBMS發(fā)現(xiàn)數(shù)據(jù)違反了完整性約束條件后要采取的違約處理行為，如拒絕（NO ACTION）執(zhí)行該操作，或者級(jí)聯(lián)（CASCADE）執(zhí)行其他操作。 |
- 主要約束分類
開發(fā)之中可以使用以下的五種約束進(jìn)行定義：
| 非空約束： | 如果使用了非空約束的話，則以后此字段的內(nèi)容不允許設(shè)置成null； |
| 唯一約束： | 即：此列的內(nèi)容不允許出現(xiàn)重復(fù)； |
| 主鍵約束： | 表示一個(gè)唯一的標(biāo)識(shí)，例如：人員ID不能重復(fù)，且不能為空； |
| 檢查約束： | 用戶自行編寫設(shè)置內(nèi)容的檢查條件； |
| 主-外鍵約束（參照完整性約束）： | 是在兩張表上進(jìn)行的關(guān)聯(lián)約束，加入關(guān)聯(lián)約束之后就產(chǎn)生父子的關(guān)系。 |
| 隱式約束 | 例如數(shù)據(jù)類型 |
?
—— 非空約束：NK
- 在正常情況下，NULL是每個(gè)屬性的合法數(shù)據(jù)值。如果說(shuō)現(xiàn)在某個(gè)字段不能為NULL，且必須存在數(shù)據(jù)，那么就可以依靠非空約束來(lái)進(jìn)行控制，這樣在數(shù)據(jù)更新時(shí)，此字段的內(nèi)容出現(xiàn)NULL時(shí)就會(huì)產(chǎn)生錯(cuò)誤。
- 非空約束不允許字段為null；
- 非空約束出現(xiàn)錯(cuò)誤時(shí)會(huì)提示完整的錯(cuò)誤位置。
- 使用非空約束：只需要在定義列的時(shí)候后面增加一個(gè)NOT NULL即可.
|
| DROP TABLE member PURGE ; CREATE TABLE member( ?? mid???? NUMBER?? , ??? name??? VARCHAR2(200)?? NOT NULL ) ; |
|
| INSERT INTO member(mid,name) VALUES(1,'wendy') |
?
—— 唯一約束：UK和CONSTRAINT子句
唯一約束,CONSTRAINT（constraint約束）
- 唯一約束（UNIQUE，簡(jiǎn)稱UK）表示的是在表中的數(shù)據(jù)不允許出現(xiàn)重復(fù)的情況
- 唯一約束可以設(shè)置NULL；
- 唯一約束的列不允許重復(fù)。
- 只需要在定義列的時(shí)候后面增加一個(gè)UUNIQUE即可.
|
| DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER?? , ??? name??? VARCHAR2(200)?? NOT NULL , ??? email?? VARCHAR2(50)??? UNIQUE ) ; |
| 向表中添加數(shù)據(jù)： INSERT INTO member(mid,name,email) VALUES(1,'t','z@qq.com') INSERT INTO member(mid,name,email) VALUES(2,'w','z@qq.com') |
| 當(dāng)添加第二個(gè)數(shù)據(jù)時(shí)候出現(xiàn)錯(cuò)誤：ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C0011099)
|
| 為唯一約束指定一個(gè)名字 CONSTRAINT uk_email UNIQUE(email) |
| DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER?? , ??? name??? VARCHAR2(200)??? NOT NULL , ??? email?? VARCHAR2(50)?, ??? CONSTRAINT uk_email????? UNIQUE (email) ) ; |
| 唯一約束是不受空類型控制. 如下兩條記錄都可以執(zhí)行 |
| INSERT INTO member(mid,name,email) VALUES(1,'t',null) INSERT INTO member(mid,name,email) VALUES(2,'w',null) |
?
主鍵約束：PK
?
- 主鍵約束：PK
- 如果一個(gè)字段即要求唯一，又不能設(shè)置為null，則可以使用主鍵約束（主鍵約束 = 非空約束 + 唯一約束），
- 主鍵約束使用PRIMARY KEY（簡(jiǎn)稱PK）進(jìn)行指定.
| 在member表中的mid字段應(yīng)該表示一個(gè)成員的唯一編號(hào)， 而這個(gè)編號(hào)即不能為空，也不能重復(fù)。 |
| 設(shè)置member表中的mid為主鍵 |
| DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER????????? PRIMARY KEY , ??? name??? VARCHAR2(200)?? NOT NULL , ??? email?? VARCHAR2(50)?, ??? CONSTRAINT uk_email UNIQUE (email) ) ; |
| 指定主鍵約束的名稱 |
| DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER?????? , ??? name??? VARCHAR2(200)?? NOT NULL , ??? email?? VARCHAR2(50)?, ??? CONSTRAINT pk_mid PRIMARY KEY (mid) , ??? CONSTRAINT uk_email UNIQUE (email) ) ; |
?
- 復(fù)合主鍵
- 在實(shí)際的開發(fā)之中，一般在一張表中只會(huì)設(shè)置一個(gè)主鍵，但是也允許為一張表設(shè)置多個(gè)主鍵，這個(gè)時(shí)候?qū)⑵浞Q為復(fù)合主鍵。在復(fù)合主鍵中，只有兩個(gè)主鍵字段的內(nèi)容完全一樣，才會(huì)發(fā)生違反約束的錯(cuò)誤。
不建議使用復(fù)合主鍵
?
?? 檢查約束：CK
- 檢查約束指的是對(duì)數(shù)據(jù)增加的條件過(guò)濾，表中的每行數(shù)據(jù)都必須滿足指定的過(guò)濾條件。
- 在進(jìn)行數(shù)據(jù)更新操作時(shí)，如果滿足檢查約束所設(shè)置的條件，數(shù)據(jù)可以成功更新，如果不滿足，則不能更新，
- 在SQL語(yǔ)句中使用CHECK（簡(jiǎn)稱CK）設(shè)置檢查約束的條件。
- 檢查約束會(huì)設(shè)置多個(gè)過(guò)濾條件，所以檢查約束過(guò)多時(shí)會(huì)影響數(shù)據(jù)更新性能。
- 在member表中增加age字段（年齡范圍是0~200歲）和sex字段（只能是男或女）
DROP TABLE member PURGE ;
CREATE TABLE member(
??? mid???? NUMBER?????? ,
??? name??? VARCHAR2(200)?? NOT NULL ,
??? email?? VARCHAR2(50)?,
??? age???? NUMBER????????? CHECK (age BETWEEN 0 AND 200) ,
??? sex???? VARCHAR2(10) ,
??? CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) ,
??? CONSTRAINT uk_email UNIQUE (email) ,
??? CONSTRAINT ck_sex?? CHECK (sex IN ('男','女'))
) ;
?
主-外鍵約束：FK（重要）
- 級(jí)聯(lián)操作：ON DELETE CASCADE、ON DELETE SET NULL；
- 使用外鍵約束后刪除表時(shí)應(yīng)先刪除子表再刪除父表；
- 在進(jìn)行外鍵設(shè)置的時(shí)候，對(duì)應(yīng)的字段，在父表中必須是主鍵或是唯一約束.
- 如果A表和B表被設(shè)置為互為外鍵，那么一張表都無(wú)法刪除.
- 這個(gè)時(shí)候只能強(qiáng)制刪除：DROP TABLE member CASCADE CONSTRAINT;
- 不建議使用，在設(shè)計(jì)表的時(shí)候就應(yīng)該考慮好先后關(guān)系
- 外鍵約束的產(chǎn)生分析
- 例如，現(xiàn)在公司要求每一位成員為公司發(fā)展提出一些更好的建議，并且希望將這些建議保存在數(shù)據(jù)表之中，那么根據(jù)這樣的需求，可以設(shè)計(jì)出如圖所示的設(shè)計(jì)模型。
- 一個(gè)成員可以提出多個(gè)建議，這是一個(gè)一對(duì)多的關(guān)系.
- 設(shè)計(jì)出了兩張數(shù)據(jù)表，兩張表的作用如下：
- 人員表：用于保存成員的基本信息（編號(hào)、姓名）；
- 建議表：保存每一個(gè)成員提出的建議內(nèi)容，所以在此表之中保存在了一個(gè)成員編號(hào)，即：通過(guò)此成員編號(hào)就可以和成員表進(jìn)行數(shù)據(jù)的關(guān)聯(lián)。
| 建立member和advice表 |
| DROP TABLE member PURGE ; DROP TABLE advice PURGE ; CREATE TABLE member( ??? mid???? NUMBER?????? , ??? name??? VARCHAR2(200)?? NOT NULL , ??? CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice( ??? adid???? NUMBER?????? , ??? content??? CLOB?? NOT NULL , ??????? mid???? NUMBER, ??? CONSTRAINT pk_adid PRIMARY KEY (adid) ) ; |
| 增加一組正確的數(shù)據(jù)； WENDY提出了2個(gè)意見，GAGA提出了3個(gè)意見 |
| INSERT INTO member(mid,name) VALUES(1,'WENDY'); INSERT INTO member(mid,name) VALUES(2,'GAGA'); INSERT INTO advice(adid,content,mid) VALUES(1,'Miss Blair, we must go or we'll be late',1); INSERT INTO advice(adid,content,mid) VALUES(2,'I've been invited to a charity event',1); INSERT INTO advice(adid,content,mid) VALUES(3,'Okay, so whose mess is it?',2); INSERT INTO advice(adid,content,mid) VALUES(4,'Can i borrow you for a minutes?',2); INSERT INTO advice(adid,content,mid) VALUES(5,'There is nothing I can do',2); |
| 查詢每個(gè)成員的完整信息和所提出的意見的數(shù)量 |
| 確定所需要的數(shù)據(jù)表： member表 advice表：統(tǒng)計(jì)建議數(shù)量 確定關(guān)聯(lián)字段：m.mid=a.mid |
| SELECT m.mid,m.name,COUNT(a.mid) FROM member m,advice a WHERE m.mid=a.mid GROUP BY m.mid,m.name; |
| 增加一個(gè)建議，member表中并不存在99的信息，這時(shí)候就會(huì)出現(xiàn)錯(cuò)誤數(shù)據(jù) |
| INSERT INTO advice(adid,content,mid) VALUES(6,q'[nothing]',99); |
| 現(xiàn)在對(duì)于表可以分為父表（member）和子表（advice），因?yàn)樽颖碇械臄?shù)據(jù)必須參考member的數(shù)據(jù)。建議提出的成員編號(hào)應(yīng)該是在member表中mid列上存在的數(shù)據(jù). |
| 為了保證表的數(shù)據(jù)的有效性，只能利用外鍵約束完成. 外鍵使用FOREIGN KEY來(lái)進(jìn)行設(shè)置 |
| 增加外鍵配置 |
| DROP TABLE member PURGE ; DROP TABLE advice PURGE ; CREATE TABLE member( ??? mid???? NUMBER?????? , ??? name??? VARCHAR2(200)?? NOT NULL , ?? ?CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice( ??? adid???? NUMBER?????? , ??? content??? CLOB?? NOT NULL , ??????? mid???? NUMBER, ??? CONSTRAINT pk_adid PRIMARY KEY (adid) , ?CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid) ) ; |
| 執(zhí)行錯(cuò)誤的數(shù)據(jù)插入： INSERT INTO advice(adid,content,mid) VALUES(6,q'[nothing]',99); |
| ORA-02291: 違反完整約束條件 (SCOTT.FK_ADID) - 未找到父項(xiàng)關(guān)鍵字 |
?
一旦為表中增加了外鍵約束，就會(huì)有新的問題：
級(jí)聯(lián)操作問題
| 問題一：刪除父表數(shù)據(jù)前需要先清出所有子表的對(duì)應(yīng)數(shù)據(jù) DELECT FROM member WHERE mid=1; |
| RA-02291: 違反完整約束條件 (SCOTT.FK_ADID) - 未找到父項(xiàng)關(guān)鍵字。 |
| 如果要?jiǎng)h除這條記錄，就必須要?jiǎng)h除子表的記錄,但是這樣的做法并不可取. |
| DELETE FROM advice WHERE mid=1; DELETE FROM member WHERE mid=1; |
| 解決方法：根據(jù)業(yè)務(wù)需求選擇合適的級(jí)聯(lián)操作 【級(jí)聯(lián)操作一】級(jí)聯(lián)刪除（ON DELETE CASCADE） 當(dāng)主數(shù)據(jù)表被刪除后，對(duì)應(yīng)的子表數(shù)據(jù)也應(yīng)該同時(shí)被清理.這樣不行，比如一個(gè)公司部門取消，不能將雇員都刪除. 【級(jí)聯(lián)操作二】級(jí)聯(lián)設(shè)置NULL（ON DELETE SET NULL） 當(dāng)主表數(shù)據(jù)被刪除后，對(duì)應(yīng)的子表數(shù)據(jù)的相應(yīng)字段的內(nèi)容會(huì)設(shè)置為null. |
| 級(jí)聯(lián)刪除配置（ON DELETE CASCADE） |
| DROP TABLE advice PURGE ; DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER?????? , ??? name??? VARCHAR2(200)?? NOT NULL , ??? CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice( ??? adid???? NUMBER?????? , ??? content??? CLOB?? NOT NULL , ??????? mid???? NUMBER, ??? CONSTRAINT pk_adid PRIMARY KEY (adid) , ?CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE CASCADE ) ; 現(xiàn)在刪除父表記錄，父表記錄以及子表記錄的信息都被刪除. DELETE FROM member WHERE mid=1; |
| 級(jí)聯(lián)更新：級(jí)聯(lián)設(shè)置NULL（ON DELETE SET NULL） |
| DROP TABLE advice PURGE ; DROP TABLE member PURGE ; CREATE TABLE member( ??? mid???? NUMBER?????? , ??? name??? VARCHAR2(200)?? NOT NULL , ???CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice( ??? adid???? NUMBER?????? , ??? content??? CLOB?? NOT NULL , ??????? mid???? NUMBER, ??? CONSTRAINT pk_adid PRIMARY KEY (adid) , ?CONSTRAINT fk_adid FOREIGN KEY (mid) REFERENCES member(mid) ON DELETE SET NULL ) ; 刪除父表中的記錄，這個(gè)時(shí)候父表中的記錄會(huì)被刪除，而子表中的記錄會(huì)變?yōu)閚ull空. DELETE FROM member WHERE mid=1; |
問題二：刪除父表時(shí)需要先將子表刪除
?
查看約束
?
- 約束依然屬于數(shù)據(jù)庫(kù)對(duì)象，可以直接利用數(shù)據(jù)字典查看。
- 約束是由數(shù)據(jù)庫(kù)自己創(chuàng)建的兌現(xiàn)個(gè)，所有對(duì)象都會(huì)在數(shù)據(jù)字典中進(jìn)行保存
- 可以利用user_constraints數(shù)據(jù)字典或者user_cons_columns數(shù)據(jù)字典查看.
| 創(chuàng)建一張member表，主鍵不設(shè)置名字 |
| CREATE TABLE member( ??? mid???? NUMBER?????? PRIMARY KEY , ??? name??? VARCHAR2(200)?? NOT NULL , ) ; |
| 向表中插入數(shù)據(jù)， |
| INSERT INTO member(mid,name) VALUES(1,'WENDY'); INSERT INTO member(mid,name) VALUES(1,'GAGA'); |
| 當(dāng)插入第二條數(shù)據(jù)時(shí)候會(huì)報(bào)錯(cuò)，ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C0011147)
|
| user_constraints查看約束信息，就能看到SYS_C0011099 Constraints_type字段可以看到約束的類型，P為主鍵，C為檢查 |
| SELECT * FROM user_constraints;
SELECT constraint_name,constraint_type,table_name FROM user_constraints ;
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name='EMP' ; |
| 要查詢約束所對(duì)應(yīng)的字段，這時(shí)候查user_cons_columns |
| SELECT * FROM user_cons_columns; |
?
修改約束（了解）
- 約束在建立表的時(shí)候一定要同時(shí)建立；
- 對(duì)于約束不建議對(duì)其進(jìn)行修改。
?
-
為表中增加約束
- 語(yǔ)法：ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 PRIMARY KEY(約束字段) ;
- 范例：為member表的mid字段增加主鍵約束
- ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ;
- 范例：為member表的age增加檢查約束
- ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200) ;
啟用／禁用約束
- 禁用約束
- ALTER TABLE 表名稱 DISABLE CONSTRAINT 約束名稱 [CASCADE];
- 范例：禁用advice表中的adid主鍵約束“pk_adid”
- ALTER TABLE advice DISABLE CONSTRAINT pk_adid ;
- 啟用約束
- ALTER TABLE 表名稱 ENABLE CONSTRAINT 約束名稱 ;
- 范例：禁用member表中的“pk_mid”約束，此字段在advice表中是外鍵
- ALTER TABLE member DISABLE CONSTRAINT pk_mid ;
- 禁用約束
刪除約束
- ALTER TABLE 表名稱 DROP CONSTRAINT 約束名稱 [CASCADE];
- 范例：刪除advice表之中的“pk_adid”約束 —— 無(wú)關(guān)聯(lián)外鍵
- ALTER TABLE advice DROP CONSTRAINT pk_adid ;
- 范例：刪除member表之中的“pk_mid”約束 —— 有關(guān)聯(lián)外鍵
- ALTER TABLE member DROP CONSTRAINT pk_mid CASCADE ;
?
?
?
轉(zhuǎn)載于:https://www.cnblogs.com/thescentedpath/p/constraint.html
總結(jié)
- 上一篇： OpenNebula学习第三节之虚拟机管
- 下一篇： Oracle DML