.jpg)
sql完整性約束有：1、“NOT NULL”非空約束，指定的列不允許為空值；2、“UNIQUE”唯一約束，指定列中沒(méi)有重復(fù)值；3、“PRIMARY KEY”主鍵約束，唯一的標(biāo)識(shí)出表的每一行；4、“FOREIGN KEY”外鍵約束；5、“CHECK”條件約束。
完整性約束是保證用戶(hù)所做的修改不會(huì)破壞數(shù)據(jù)的一致性，是保護(hù)數(shù)據(jù)正確性和相容性的一種手段。
常見(jiàn)的5種約束：
NOT?NULL：非空約束C，指定的列不允許為空值
UNIQUE：唯一約束U，指定的列中沒(méi)有重復(fù)值，或該表中每一個(gè)值或者每一組值都將是唯一的
PRIMARY?KEY：主鍵約束P，唯一的標(biāo)識(shí)出表的每一行,且不允許空值,一個(gè)表只能有一個(gè)主鍵約束
FOREIGN?KEY：外鍵約束R，一個(gè)表中的列引用了其它表中的列，使得存在依賴(lài)關(guān)系，可以指向引用自身的列
CHECK：條件約束C，指定該列是否滿(mǎn)足某個(gè)條件
非空約束 NK
create?table?member( ????mid?number,???? ????name?varchar2(200)?not?null ????);
插入空?qǐng)?bào)錯(cuò)：
SQL>?insert?into?member(mid,name)?values(1,null); * 第?1?行出現(xiàn)錯(cuò)誤: ORA-01400:?無(wú)法將?NULL?插入?("SCOTT"."MEMBER"."NAME")
?唯一約束 UK
drop?table?member?purge;create?table?member( ????mid?number, ????name?varchar2(200)?not?null, ????email?varchar2(50)?unique ????);
插入報(bào)錯(cuò)
SQL>?insert?into?member(mid,name,email)?values?(1,'scott','scott@163.com');
已創(chuàng)建 1 行。
SQL>?insert?into?member(mid,name,email)?values?(2,'jack','scott@163.com'); insert?into?member(mid,name,email)?values?(2,'jack','scott@163.com') * 第?1?行出現(xiàn)錯(cuò)誤: ORA-00001:?違反唯一約束條件?(SCOTT.SYS_C0010891)
給約束指定名字
這錯(cuò)誤并沒(méi)有像之前的非空約束那樣，準(zhǔn)確告訴用戶(hù)哪個(gè)字段出現(xiàn)了問(wèn)題，我們可以給這個(gè)約束指定一個(gè)名字 ?
create?table?member( ????mid?number, ????name?varchar2(200)?not?null, ????email?varchar2(50),???? ????constraint?uk_email?unique(email) ????);
主鍵約束 PK
主鍵約束=非空約束+唯一約束
create?table?member( ????mid?number, ????name?varchar2(200)?not?null, ????email?varchar2(50),???? ????constraint?pk_number?primary?key(mid),???? ????constraint?uk_email?unique(email) ????);
插入錯(cuò)誤的數(shù)據(jù)報(bào)錯(cuò)：
SQL>?insert?into?member(mid,name,email)?values?(1,'jack','scott@163.com');
已創(chuàng)建 1 行
SQL>?insert?into?member(mid,name,email)?values?(1,'jack','jack@163.com'); insert?into?member(mid,name,email)?values?(1,'jack','jack@163.com') * 第?1?行出現(xiàn)錯(cuò)誤: ORA-00001:?違反唯一約束條件?(SCOTT.PK_NUMBER)
檢查約束 CK
create?table?member( ???mid?number,???? ???name?varchar2(200)?not?null,???? ???age?number?check(age?between?0?and?200),???? ???sex?varchar2(10),???? ???email?varchar2(50),???? ???constraint?pk_number?primary?key(mid),???? ???constraint?uk_email?unique(email),???? ???constraint?ck_sex?check(sex?in('男','女'))???? ???);
插入錯(cuò)誤數(shù)據(jù)報(bào)錯(cuò)：
SQL>?insert?into?member(mid,name,age,sex,email)?values?(1,'Jack','300','無(wú)','jack@163.com'); insert?into?member(mid,name,age,sex,email)?values?(1,'Jack','300','無(wú)','jack@163.com') * 第?1?行出現(xiàn)錯(cuò)誤: ORA-02290:?違反檢查約束條件?(SCOTT.CK_SEX)
主外鍵約束 ?FK
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_mid?foreign?key(mid)?references?member(mid) ???????);
正確插入數(shù)據(jù)，mid在附表中已經(jīng)存在
insert?into?member(mid,name)?values?(1,'Scott'); insert?into?member(mid,name)?values?(2,'Jack'); insert?into?advice(adid,content,mid)?values?(1,'test',1); insert?into?advice(adid,content,mid)?values?(2,'test',2); commit;
插入一條父表中沒(méi)有的mid數(shù)據(jù)
SQL>?insert?into?advice(adid,content,mid)?values?(3,'test',3); insert?into?advice(adid,content,mid)?values?(3,'test',3) * 第?1?行出現(xiàn)錯(cuò)誤: ORA-02291:?違反完整約束條件?(SCOTT.FK_MID)?-?未找到父項(xiàng)關(guān)鍵字
?采用主外鍵約束會(huì)在表的刪除和數(shù)據(jù)刪除方面存在新的問(wèn)題
刪除父表得先刪除子表
SQL>?drop?table?member;drop?table?member??????????? * 第?1?行出現(xiàn)錯(cuò)誤: ORA-02449:?表中的唯一/主鍵被外鍵引用
強(qiáng)制性刪除父表，這樣就不能使用purge選項(xiàng)了，并且字表中的主外鍵約束也將不復(fù)存在
SQL>?drop?table?member?cascade?constraint; 表已刪除。 SQL>?insert?into?advice(adid,content,mid)?values?(3,'test',3)；
已創(chuàng)建 1 行
刪除父表的數(shù)據(jù)的先刪除字表中的數(shù)據(jù)
SQL>?delete?from?member?where?mid=1; delete?from?member?where?mid=1 * 第?1?行出現(xiàn)錯(cuò)誤: ORA-02292:?違反完整約束條件?(SCOTT.FK_MID)?-?已找到子記錄
除了這種方法外還可以設(shè)置級(jí)聯(lián)操作子句
on delete cascade
create?table?advice( ???????adid?number, ???????content?clob?not?null, ???????mid?number,??????? ???????constraint?pk_adid?primary?key(adid),??????? ???????constraint?fk_mid?foreign?key(mid)?references?member(mid)?on?delete?cascade ???????);
這樣的話(huà)，刪除附表數(shù)據(jù)，會(huì)把字表中的數(shù)據(jù)也給刪除。
on delete set null
create?table?advice(??????? adid?number,??????? content?clob?not?null,??????? mid?number,??????? constraint?pk_adid?primary?key(adid),??????? constraint?fk_mid?foreign?key(mid)?references?member(mid)?on?delete?set?null?????? ?);
這樣刪除父表中的數(shù)據(jù)，子表中的對(duì)應(yīng)的關(guān)聯(lián)字段將被設(shè)置為空
SQL>?delete?from?member?where?mid=1; 已刪除?1?行。 SQL>?select?*?from?advice; ??????ADID?CONTENT?????????MID ----------?----------?---------- ?test test????????????2
查看約束
SQL>??select?constraint_name,constraint_type,table_name??from?user_constraints;? CONSTRAINT_NAME????????????C? TABLE_NAME ------------------------------?-?------------------------------ FK_DEPTNO??????????????????R? ??EMP PK_EMP?????????????????????P? ??EMP PK_DEPT????????????????????P? ??DEPT
R外鍵約束，C檢查約束，Q唯一約束，P主鍵約束
修改約束
增加約束
alter?table?member?add?constraint?pk_mid?primary?key(mid); alter?table?member?add?constraint?ck_age?check(age?between?0?and?200); alter?table?member?modify?(name?varchar2(50)?not?null);
需要注意的是非空約束使用的是modify的方式
禁用/啟用約束
非外鍵
alter?table?member?disable?constraint?ck_age; alter?table?member?enable?constraint?ck_age;
外鍵
alter?table?member?disable?constraint?pk_mid?cascade;
刪除約束
alter?table?member?drop?constraint?pk_mid?cascade; alter?table?member?drop?constraint?ck_age;
.png)
