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 約束強制列不接受null值。

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

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

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

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

成功的報錯了，除非你有給id定一個default（默認(rèn)值），否則在insert時不給id賦值一定會報錯。

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約束的字段一定要賦值，沒有not null約束的比如city字段在insert時不賦值會自動為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約束明明加進去了，但是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)進入表中了。。。

6．3．2

百度以后，發(fā)現(xiàn)這個mysql本身的問題，想要達到約束效果可以使用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約束：

用來唯一標(biāo)識數(shù)據(jù)庫表中的每條記錄；主鍵必須包含唯一的值且不能含有null值。與6．1unique約束不同的是，unique約束能同時對好幾個字段增加唯一約束，而primary約束只能對某一列創(chuàng)建唯一約束。在一個表中只能有一個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ù)，所以報錯。

先將表中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的更新和刪除時將會聯(lián)動表a中外鍵與其關(guān)聯(lián)對應(yīng)的記錄做更新或刪除操作

6．6．2

在做這個操作時，表newstudent和表test都是有數(shù)據(jù)的，所以就一直報錯

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＇

通過這兩步，將表中的數(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

此時，給表newstudent增加外鍵id；且此外鍵的參照為表test的id項；這時表test為主表；表newstudent為從表

重點：主表和從表一定要分清楚，因為主表的更新和刪除時將會聯(lián)動從表中外鍵與其關(guān)聯(lián)對應(yīng)的記錄做更新或刪除操作

6．6．4

之前沒有注意，在兩個表都為空表時，先給從表newstudent賦值插入，結(jié)果就是報錯

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，＇語文＇）；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會報錯，因為這個時候test表沒有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é)果也是報錯，因為id有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)對主表進行刪除或更新操作時，從表也會一起刪除或更新；

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｀））

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

我們在更新與刪除時遇到的外鍵約束解決方案分別對應(yīng)設(shè)置Update rule與Delete rule。有如下四個選項：

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

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

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

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

要點：newstudent＿ibfk＿1是在之前的報錯中找到的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 ｜ 王五      ｜ 語文   ｜    90 ｜｜  3 ｜ 王五的    ｜ 語文   ｜    90 ｜＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

test表的id從2變成了3；

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

此時newstudent表的id也從2變成了3；（此時沒有直接對newstudent表有過任何操作）

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 ｜ 王五   ｜ 語文   ｜    90 ｜＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋
select ＊ from newstudent；＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ userid ｜ id ｜ name   ｜ gender ｜ score ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜    100 ｜  1 ｜ 張三   ｜ 男     ｜ 65    ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

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