1、約束概述
約束的目的是為了保證數(shù)據(jù)的完整性與一致性。
按照約束的范圍劃分：
列級(jí)約束：只對(duì)一個(gè)數(shù)據(jù)列建立約束忧吟。既可以在列定義時(shí)聲明，也可以在列定義后聲明斩披；
表級(jí)約束：對(duì)多個(gè)數(shù)據(jù)列建立約束溜族。只能在列定義后聲明；
按照約束的功能劃分：
非空約束——NOT NULL
主鍵約束——PRIMARY KEY
唯一約束——UNIQUE KEY
默認(rèn)約束——DEFAULT
外鍵約束——FOREIGN KEY
在實(shí)際開發(fā)中垦沉，表級(jí)約束很少用到煌抒，更多的會(huì)使用列級(jí)約束；除此之外厕倍，在所有的約束中寡壮，并不一定每種功能的約束都存在著表級(jí)或列級(jí)之分。其中，非空約束和默認(rèn)約束就不存在表級(jí)約束况既，它們只有列級(jí)約束这溅；而對(duì)于主鍵約束、唯一約束棒仍、外鍵約束都可以作為表級(jí)約束或列級(jí)約束悲靴。
2、外鍵約束的要求解析
前四種約束的用法及效果可參見操作數(shù)據(jù)表降狠，現(xiàn)在來(lái)看一下外鍵約束：外鍵約束即“FOREIGN KEYp”对竣，可以保證數(shù)據(jù)的完整性與一致性庇楞，實(shí)現(xiàn)數(shù)據(jù)表一對(duì)一或一對(duì)多的關(guān)系榜配，這也是許多數(shù)據(jù)庫(kù)被稱為“關(guān)系型數(shù)據(jù)庫(kù)”的根本原因。
創(chuàng)建外鍵約束需要滿足如下要求：
- 父表(包含參照列)和子表(包含外鍵列)必須使用相同的存儲(chǔ)引擎吕晌，且存儲(chǔ)引擎只能為InnoDB蛋褥，而且禁止使用臨時(shí)表；
- 外鍵列和參照列必須具有相似的數(shù)據(jù)類型睛驳，其中：數(shù)字的長(zhǎng)度或是否有符號(hào)位必須相同烙心，而字符的則長(zhǎng)度可以不同；
- 外鍵列和參照列必須創(chuàng)建索引乏沸，如果外鍵列不存在索引淫茵，MySQL將自動(dòng)創(chuàng)建索引。
創(chuàng)建父表“provinces”蹬跃，并檢查其存儲(chǔ)引擎：
創(chuàng)建子表“users”匙瘪，此表的目的是記錄用戶的省份信息，在使用外鍵約束后蝶缀，只需要父表的“id”就可以獲取相應(yīng)的省份丹喻，但是根據(jù)要求對(duì)于數(shù)字的長(zhǎng)度必須相同，因此在使用數(shù)據(jù)類型“BIGINT”后翁都，系統(tǒng)提示錯(cuò)誤：
此時(shí)已將數(shù)據(jù)類型修改為相同的“SMALLINT”碍论，但系統(tǒng)仍然提示錯(cuò)誤，原因是父表中注明“UNSIGNED”柄慰，即無(wú)符號(hào)位鳍悠，而子表中并沒有注明，因此出錯(cuò)：
最終完成子表的創(chuàng)建：
輸入“SHOW INDEXES FROM tbl_name”可查看該表的索引情況：
由于“id”字段使用了主鍵坐搔，而主鍵會(huì)自動(dòng)創(chuàng)建索引藏研，因此參照列已經(jīng)具有了索引，再來(lái)看外鍵列：
可見具有兩個(gè)索引薯蝎，一個(gè)是“id”字段遥倦，另一個(gè)則是外鍵列“p_id”字段，查看該數(shù)據(jù)表的結(jié)構(gòu)可以發(fā)現(xiàn)：
系統(tǒng)已自動(dòng)為其創(chuàng)建了索引。
注意：
父表——子表所參照的表“provinces”
子表——含有外鍵“FOREIGN KEY”的表“users”
參照列——父表中“id”字段列
外鍵列——子表中“p_id”字段列
3袒哥、外鍵約束的參照操作
外鍵約束的參照操作缩筛，簡(jiǎn)單理解就是在創(chuàng)建外鍵約束之后，更新父表時(shí)堡称，子表是否進(jìn)行同樣的操作瞎抛，共有如下4種：
- CASCADE：從父表刪除或更新且自動(dòng)刪除或更新子表中匹配的行；
- SET NULL：從父表刪除或更新行却紧，并設(shè)置子表中的外鍵列為“NULL”桐臊。如果使用該選項(xiàng)，必須保證子表列沒有指定“NOT NLUU”晓殊；
- RESTRICT：拒絕對(duì)父表的刪除或更新操作断凶；
- NO ACTION：標(biāo)準(zhǔn)SQL的關(guān)鍵字，在MySQL中與“RESTRICT”作用相同巫俺。
以“CASCADE”為例進(jìn)行演示认烁，創(chuàng)建子表“users1”：
之后，需要在兩張數(shù)據(jù)表中添加記錄介汹，要注意却嗡，必須先在父表中添加記錄，子表才能有所參照：
在父表中添加三個(gè)省份信息嘹承，之后向子表中添加用戶信息窗价，只需要寫入用戶名及父表中省份的“id”即可：
之所以在添加“John”用戶時(shí)出錯(cuò)，是因?yàn)楦副碇懈静淮嬖凇癷d”為9的省份叹卷，因而報(bào)錯(cuò)撼港。查看該數(shù)據(jù)表的結(jié)構(gòu)可發(fā)現(xiàn)：
“id”排列沒有序號(hào)3是因?yàn)椋m然之前輸入記錄時(shí)“‘John’豪娜，9”報(bào)錯(cuò)沒有計(jì)入餐胀，但是編號(hào)已經(jīng)自動(dòng)遞增一位，因此最終的用戶“id”為1瘤载、2否灾、4、5鸣奔。此時(shí)刪除父表中“id”為3的省份墨技，看子表是否也會(huì)發(fā)生改變：
可見父表中“id”為3的省份已被刪除，而子表中“p_id”為3的用戶“Rose”也一同被刪除了挎狸。
注意：
關(guān)于外鍵約束扣汪，在實(shí)際的開發(fā)過(guò)程中，其實(shí)很少使用物理性的外鍵約束锨匆，更多的會(huì)去使用邏輯性的外鍵約束崭别，因?yàn)槲锢硇缘耐怄I約束只有InnoDB這種引擎才會(huì)支持冬筒，然而另一種MyISAM引擎則不支持。換句話說(shuō)茅主，如果創(chuàng)建一張數(shù)據(jù)表舞痰，其存儲(chǔ)引擎為MyISAM，但又想使用這種物理性的外鍵約束的話诀姚，其實(shí)是不可能實(shí)現(xiàn)的响牛，因此，在實(shí)際的項(xiàng)目開發(fā)中赫段，不會(huì)去定義物理性的外鍵呀打。所謂的邏輯性外鍵指的就是在創(chuàng)建數(shù)據(jù)表之前，已經(jīng)定義好多張數(shù)據(jù)表的結(jié)構(gòu)糯笙，而不去使用“FOREIGN KEY”這個(gè)關(guān)鍵詞贬丛。
4、修改數(shù)據(jù)表——添加／刪除列
添加單列
ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name]
** “FIRST col_name”指所插入的列位于指定列之前炬丸；“AFTER col_name”指所插入的列位于指定列之后瘫寝；如果都省略蜒蕾，則默認(rèn)在所有列之后插入該列稠炬。**
首先查看“users1”的數(shù)據(jù)結(jié)構(gòu)，之后插入數(shù)據(jù)列“age”咪啡，且不指定其位置首启，再次查看該表結(jié)構(gòu)可發(fā)現(xiàn)，新添加的列“age”確實(shí)位于所有列之后撤摸，排在末尾：
添加一列“password”毅桃，指定其位置在“username”列之后：
再添加一列“truename”，指定其位置在所有列之前：
添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……)
添加多列時(shí)准夷，列名與定義信息要放在小括號(hào)內(nèi)钥飞，且不能指定位置，只能放在已有列的末尾
一次性向數(shù)據(jù)表“users1”中添加多列：
刪除列
ALTER TABLE tbl_name DROP [COLUMN] col_name , ……
可以刪除一列衫嵌，可以刪除多列读宙，還可以在刪除的同時(shí)新增列
刪除“username”列：
刪除“age”列同時(shí)，新增“username”列：
一次性刪除多列：
5楔绞、修改數(shù)據(jù)表——添加／刪除約束
添加主鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……)
其中“CONSTRAINT”關(guān)鍵字可以自定義主鍵名结闸，而“index_type”索引類型后面會(huì)講到。
創(chuàng)建新的數(shù)據(jù)表“users2”酒朵，且不設(shè)置主鍵：
新增一列“id”桦锄，且為其添加主鍵，主鍵名為“PK_users2_id”：
添加唯一約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……)
為“username”添加唯一約束：
添加外鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition
為“p_id”添加外鍵約束：
添加默認(rèn)約束
ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal
為新建字段“age”添加默認(rèn)約束：
刪除主鍵約束
ALTER TABLE tbl_name DROP PRIMARY KEY
刪除唯一約束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
**由于一張數(shù)據(jù)表可以有多個(gè)唯一約束蔫耽，因此刪除時(shí)需要添加索引名 **
首先查看“username”字段上唯一約束的索引名：
刪除的“username”是索引名即唯一約束结耀，而不是字段：
刪除外鍵約束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
**注意刪除時(shí)需要添加外鍵約束名 **
首先查看“p_id”字段上的外鍵約束名：
刪除“p_id”字段上的外鍵約束：
可見已經(jīng)沒有“FOREIGN KEY”等信息：
刪除默認(rèn)約束
ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT
刪除字段“age”的默認(rèn)約束：
6、修改數(shù)據(jù)表——修改列定義／更名操作
修改列定義
ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name]
修改字段“id”的數(shù)據(jù)類型為“TINYINT”并將其挪至數(shù)據(jù)表起始位置：
注意：
由范圍較大的數(shù)據(jù)類型（例如“SMALLINT”）修改成范圍較小的數(shù)據(jù)類型（例如“TINYINT”），可能會(huì)造成數(shù)據(jù)的丟失图甜。
修改列名稱
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name]
使用“CHANGE”關(guān)鍵字不僅包含“MODIFY”關(guān)鍵字的功能香伴，還可以修改字段名稱。
修改字段“p_id”的名稱為“pid”具则，數(shù)據(jù)類型為“TINYINT”并將其挪至“age”字段的后面：
修改數(shù)據(jù)表名稱
方法一：
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法二：
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……
使用方法一將數(shù)據(jù)表“users2”名稱修改為“users3”：
使用方法二將數(shù)據(jù)表“users3”名稱修改為“users2”：
注意：
實(shí)際上應(yīng)盡量少的使用數(shù)據(jù)列及數(shù)據(jù)表的更名操作即纲，因?yàn)楫?dāng)創(chuàng)建了索引或使用過(guò)視圖、存儲(chǔ)過(guò)程之后博肋，在表名或列名被引用的情況下低斋，修改其名稱可能會(huì)導(dǎo)致視圖或存儲(chǔ)過(guò)程等無(wú)法正常工作。
7匪凡、修改數(shù)據(jù)表的SQL語(yǔ)句匯總：
添加／刪除列
添加單列：ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name];
添加多列：ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……);
刪除列：ALTER TABLE tbl_name DROP [COLUMN] col_name , ……;添加／刪除約束
添加主鍵約束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……);
添加唯一約束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……);
添加外鍵約束：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition;
添加默認(rèn)約束：ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal;
刪除主鍵約束：ALTER TABLE tbl_name DROP PRIMARY KEY;
刪除唯一約束：ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
刪除外鍵約束：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
刪除默認(rèn)約束：ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT;修改列定義／更名操作
修改列定義：ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name];
修改列名稱：ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name];
修改數(shù)據(jù)表名稱
方法一：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
方法二：RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……;
版權(quán)聲明：歡迎轉(zhuǎn)載膊畴，歡迎擴(kuò)散，但轉(zhuǎn)載時(shí)請(qǐng)標(biāo)明作者以及原文出處病游，謝謝合作邮旷！ ↓↓↓
