6．1．3

刪除unique約束

alter table persons drop index uc＿PersonID；Query OK， 3 rows affected （0．02 sec）Records： 3  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．2 not null約束

6．2．1

not null 約束強(qiáng)制列不接受null值。

再拿6．1．3的表persons做說(shuō)明，可以看到id和lastname字段是存在not null約束的，所以在insert時(shí)必須要傳入這兩個(gè)字段的值

insert into persons（id，lastname） values （3，＇q＇）；Query OK， 1 row affected （0．00 sec）

插入成功，下面試一下insert時(shí)不給id賦值的情況：

insert into persons（lastname） values （＇q＇）；ERROR 1364 （HY000）： Field ＇id＇ doesn＇t have a default value

成功的報(bào)錯(cuò)了，除非你有給id定一個(gè)default（默認(rèn)值），否則在insert時(shí)不給id賦值一定會(huì)報(bào)錯(cuò)。

select ＊ from persons；＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜ id ｜ lastname ｜ firstName ｜ city ｜＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜  1 ｜ z        ｜ q         ｜ NULL ｜｜  2 ｜ z        ｜ q         ｜ NULL ｜｜  2 ｜ zz       ｜ q         ｜ NULL ｜｜  3 ｜ q        ｜ NULL      ｜ NULL ｜＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋

總結(jié)：有not null約束的字段一定要賦值，沒(méi)有not null約束的比如city字段在insert時(shí)不賦值會(huì)自動(dòng)為null

6．2．2

刪除not null 約束

alter table persons modify id int null；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．3 check 約束

6．3．1

ALTER TABLE Persons ADD CHECK （id＞0）；insert into persons（id，lastname） values （－2，＇qq＇）；Query OK， 1 row affected （0．00 sec）

非常奇怪，check約束明明加進(jìn)去了，但是id＝－2還可以被插入；

select ＊ from persons；＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜ id   ｜ lastname ｜ firstName ｜ city ｜＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜    1 ｜ z        ｜ q         ｜ NULL ｜｜    2 ｜ z        ｜ q         ｜ NULL ｜｜    2 ｜ zz       ｜ q         ｜ NULL ｜｜    3 ｜ q        ｜ NULL      ｜ NULL ｜｜   －2 ｜ qq       ｜ NULL      ｜ NULL ｜＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋

剛剛insert的數(shù)據(jù)已經(jīng)進(jìn)入表中了。。。

6．3．2

百度以后，發(fā)現(xiàn)這個(gè)mysql本身的問(wèn)題，想要達(dá)到約束效果可以使用type＝enum（）；具體方法如下：

在原表基礎(chǔ)上加入gender字段，規(guī)定gender只能為male或者female；

alter table persons add gender enum（＇male＇ ，＇female＇）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type                  ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）               ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255）          ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255）          ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255）          ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ enum（＇male＇，＇female＇） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

表persons的gender成功變?yōu)閠ype為enum（＇male＇ ，＇female＇）；

insert into persons1（id，lastname，gender） values （3，＇qqq＇，＇male1＇）；ERROR 1265 （01000）： Data truncated for column ＇gender＇ at row 1insert into persons1（id，lastname，gender） values （3，＇qqq＇，＇male＇）；Query OK， 1 row affected （0．00 sec）

6．3．3

刪除enum（＇male＇ ，＇female＇）約束

alter table persons modify gender varchar（255）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．4 default 約束

6．4．1

創(chuàng)建default 約束

alter table persons alter city set default ＇上海＇；Query OK， 0 rows affected （0．01 sec）Records： 0  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ 上海    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．4．2

刪除default 約束

alter table persons alter city drop  default；Query OK， 0 rows affected （0．01 sec）Records： 0  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．5 primary key

primary key約束：

用來(lái)唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄；主鍵必須包含唯一的值且不能含有null值。與6．1unique約束不同的是，unique約束能同時(shí)對(duì)好幾個(gè)字段增加唯一約束，而primary約束只能對(duì)某一列創(chuàng)建唯一約束。在一個(gè)表中只能有一個(gè)primary約束。

6．5．1創(chuàng)建primary 約束

alter table persons add primary key（id）；ERROR 1062 （23000）： Duplicate entry ＇2＇ for key ＇PRIMARY＇

原因：給表persons的id字段增加primary約束，這需要id字段的內(nèi)容保持唯一性，而現(xiàn)在id＝2有好幾條數(shù)據(jù)，所以報(bào)錯(cuò)。

先將表中id＝2的字段刪除，再給id增加primary約束

delete from persons where id＝2；alter table persons add primary key（id）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜ PRI ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．5．2 刪除primary 約束

alter table persons drop primary key；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．6 foreign key

6．6．1

ALTER TABLE a ADD FOREIGN KEY （P＿Id） REFERENCES b（P＿Id）

給表a增加外鍵P＿Id；且此外鍵的參照為表b的P＿Id列；關(guān)聯(lián)的操作為刪除和更新；

注意：表b為為主表，表a為從表，表b的更新和刪除時(shí)將會(huì)聯(lián)動(dòng)表a中外鍵與其關(guān)聯(lián)對(duì)應(yīng)的記錄做更新或刪除操作

6．6．2

在做這個(gè)操作時(shí)，表newstudent和表test都是有數(shù)據(jù)的，所以就一直報(bào)錯(cuò)

alter table newstudent add foreign key （id） references testdatabase．test（id）；ERROR 3734 （HY000）： Failed to add the foreign key constraint． Missing column ＇userid＇ for constraint ＇newstudent＿ibfk＿1＇ in the referenced table ＇test＇

通過(guò)這兩步，將表中的數(shù)據(jù)都刪除；

delete from newstudent；Query OK， 7 rows affected （0．01 sec）delete from test；Query OK， 7 rows affected （0．01 sec）

6．6．3

alter table newstudent add foreign key （id） references testdatabase．test（id）；Query OK， 0 rows affected （0．05 sec）Records： 0  Duplicates： 0  Warnings： 0

此時(shí)，給表newstudent增加外鍵id；且此外鍵的參照為表test的id項(xiàng)；這時(shí)表test為主表；表newstudent為從表

重點(diǎn)：主表和從表一定要分清楚，因?yàn)橹鞅淼母�新和刪除時(shí)將會(huì)聯(lián)動(dòng)從表中外鍵與其關(guān)聯(lián)對(duì)應(yīng)的記錄做更新或刪除操作

6．6．4

之前沒(méi)有注意，在兩個(gè)表都為空表時(shí)，先給從表newstudent賦值插入，結(jié)果就是報(bào)錯(cuò)

insert into newstudent（userid，id，name，gender，score）  values（100，2，＇張三我＇，＇男＇，85）；ERROR 1452 （23000）： Cannot add or update a child row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

先給test賦值，id＝2；再給newstudent賦值id＝2的數(shù)據(jù)，就可以成功插入

insert into test（id，name，score，course） values（2，＇王五的＇，90，＇語(yǔ)文＇）；Query OK， 1 row affected （0．00 sec）insert into newstudent（userid，id，name，gender，score）  values（100，2，＇張三我＇，＇男＇，85）；Query OK， 1 row affected （0．00 sec）

6．6．5

如果給newstudent賦值id＝3會(huì)報(bào)錯(cuò)，因?yàn)檫@個(gè)時(shí)候test表沒(méi)有id＝3的數(shù)據(jù)

insert into newstudent（userid，id，name，gender，score）  values（100，3，＇張三我＇，＇男＇，85）；ERROR 1452 （23000）： Cannot add or update a child row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

那在重新給newstudent賦值id＝1的數(shù)據(jù)呢？結(jié)果也是報(bào)錯(cuò)，因?yàn)閕d有primary約束，不能重復(fù)插入

insert into newstudent（userid，id，name，gender，score）  values（100，1，＇張三我＇，＇男＇，85）；ERROR 1062 （23000）： Duplicate entry ＇1＇ for key ＇PRIMARY＇

6．6．6

外鍵起到的作用就是保持連接的數(shù)據(jù)表的一致性，當(dāng)對(duì)主表進(jìn)行刪除或更新操作時(shí)，從表也會(huì)一起刪除或更新；

update test set id＝3；ERROR 1451 （23000）： Cannot delete or update a parent row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

不能刪除或更新這一行，存在外鍵約束

我們?cè)诟�新與刪除時(shí)遇到的外鍵約束解決方案分別對(duì)應(yīng)設(shè)置Update rule與Delete rule。有如下四個(gè)選項(xiàng)：

1．CASCADE：從父表刪除或更新且自動(dòng)刪除或更新子表中匹配的行。

2．SET NULL：從父表刪除或更新行，并設(shè)置子表中的外鍵列為NULL。如果使用該選項(xiàng)，必須保證子表列沒(méi)有指定NOT NULL。

3．RESTRICT：拒絕對(duì)父表的刪除或更新操作。

4．NO ACTION：標(biāo)準(zhǔn)SQL的關(guān)鍵字，在MySQL中與RESTRICT相同。

6．6．7

所以將foreign約束刪除后重新構(gòu)建：

alter table newstudent drop foreign key newstudent＿ibfk＿1；Query OK， 0 rows affected （0．04 sec）Records： 0  Duplicates： 0  Warnings： 0

要點(diǎn)：newstudent＿ibfk＿1是在之前的報(bào)錯(cuò)中找到的CONSTRAINT ｀newstudent＿ibfk＿1｀

delete from test；Query OK， 0 rows affected （0．00 sec）delete from newstudent；Query OK， 2 rows affected （0．00 sec）

將表中的內(nèi)容刪除干凈

6．6．8

alter table newstudent add foreign key （id） references testdatabase．test（id） on update cascade on delete cascade；Query OK， 0 rows affected （0．04 sec）Records： 0  Duplicates： 0  Warnings： 0

on update cascade表示更新關(guān)聯(lián)；on delete cascade表示刪除關(guān)聯(lián)。

6．6．9

將表test的id＝2更新為id＝3，看看表newstudent和表test有什么變化

update test set id＝3 where id＝2；Query OK， 1 row affected （0．00 sec）Rows matched： 1  Changed： 1  Warnings： 0
select ＊ from test；＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ id ｜ name      ｜ course ｜ score ｜＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜  1 ｜ 王五      ｜ 語(yǔ)文   ｜    90 ｜｜  3 ｜ 王五的    ｜ 語(yǔ)文   ｜    90 ｜＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

test表的id從2變成了3；

select ＊ from newstudent；＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ userid ｜ id ｜ name      ｜ gender ｜ score ｜＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜    100 ｜  1 ｜ 張三      ｜ 男     ｜ 65    ｜｜    100 ｜  3 ｜ 張三我    ｜ 男     ｜ 85    ｜＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

此時(shí)newstudent表的id也從2變成了3；（此時(shí)沒(méi)有直接對(duì)newstudent表有過(guò)任何操作）

6．6．10

delete from test where id＝3；Query OK， 1 row affected （0．00 sec）

將主表test中id＝3的刪除

select ＊ from test；＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ id ｜ name   ｜ course ｜ score ｜＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜  1 ｜ 王五   ｜ 語(yǔ)文   ｜    90 ｜＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋
select ＊ from newstudent；＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ userid ｜ id ｜ name   ｜ gender ｜ score ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜    100 ｜  1 ｜ 張三   ｜ 男     ｜ 65    ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

這時(shí)表newstudent的id＝3的數(shù)據(jù)也刪除了