数据库完整性约束
完整性約束
????????? ——數(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