王麗娟 吳東明

摘 要：在實(shí)際開(kāi)發(fā)的項(xiàng)目中，一個(gè)健壯數(shù)據(jù)庫(kù)中的數(shù)據(jù)一定有很好的完整性約束。在MySQL中，創(chuàng)建數(shù)據(jù)表和修改數(shù)據(jù)表時(shí)可以對(duì)表的各列進(jìn)行一些操作，用以約束用戶(hù)對(duì)表進(jìn)行非法的記錄插入和更新。文章將對(duì)創(chuàng)建表時(shí)直接加約束和修改表時(shí)添加約束以及刪除約束進(jìn)行研究。

關(guān)鍵詞：完整性；主鍵；外鍵；唯一鍵；非空；默認(rèn)值

中圖分類(lèi)號(hào)：TP311.1 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：2095-2945（2019）02-0072-02

Abstract： In a real-world development project， the data in a robust database must have good integrity constraints. In MySQL， when you create and modify a data table， you can perform operations on its columns to constrain the user from illegally inserting and updating records on the table. The paper will study how to directly constrain when creating a table， and how to add constraints and delete constraints when modifying a table.

Keywords： integrity； primary key； foreign key； unique key； non-null； default value

為了防止在數(shù)據(jù)表中插入錯(cuò)誤的數(shù)據(jù)，在MySQL中，定義了一些維護(hù)數(shù)據(jù)庫(kù)完整性的規(guī)則，即數(shù)據(jù)表的約束。常用的約束有主鍵約束、外鍵約束、唯一鍵約束、非空約束和默認(rèn)值約束，下面將對(duì)創(chuàng)建和刪除表的約束進(jìn)行論述。

1 創(chuàng)建表的時(shí)候直接加約束

1.1 主鍵約束

主鍵約束可以唯一標(biāo)識(shí)表中的記錄，通過(guò)primary key定義，主鍵約束分為兩種，具體如下：

（1）單字段主鍵

create table stuinfo（sid int primary key，sname varchar（10），sgender char（1））；

create table course（cid int，cname varchar（30），constraint pk_cid primary key（cid））；

后一種創(chuàng)建表時(shí)加主鍵約束可以使用constraint給約束起別名，但是只能放在最前、最后或者某個(gè)字段定義結(jié)束（create table course（cid int，constraint pk_cid primary key（cid），scname varchar（30））；）并加逗號(hào)分隔。在MySQL中給主鍵約束起約束名沒(méi)有太大意義，因?yàn)橐粋€(gè)表只有一個(gè)主鍵，刪除主鍵約束時(shí)用不到約束的名字。

（2）多字段主鍵

create table score（sid int，cid int，grade float，primary key（stuid，cid））；

當(dāng)多個(gè)字段作為主鍵時(shí)，可在primary key后面的括號(hào)里把需要做主鍵的字段羅列出來(lái)即可，但是只能放在最前面或者字段定義結(jié)束并用逗號(hào)分隔。

1.2 唯一性約束

創(chuàng)建唯一性約束時(shí)，可以在字段的描述后面直接加unique，或者在定義表的任意位置（完整描述）添加unique（columnname），默認(rèn)的約束名稱(chēng)是該列的名字，但是如果修改了這個(gè)列的名稱(chēng)，約束的名稱(chēng)是不會(huì)修改的。如果想給約束起名字，可以使用constraint 約束名 unique（columnname）。

Create table t5（id int primary key，stuid int， constraint uq_stuid unique，stutel int unique）；

1.3 外鍵約束

外鍵約束是將主表的某些列和子表的某些列關(guān)聯(lián)在一起，其目的是為了不讓子表的列隨意增加主表列中不存在的項(xiàng)，如stuinfo表中沒(méi)有129501111這個(gè)學(xué)號(hào)，那么子表score表中就不能插入這個(gè)學(xué)號(hào)的列，主表course表中沒(méi)有08113216這個(gè)課程編號(hào)，子表score表中就不能插入這個(gè)編號(hào)的列。

Create table score（sid int，cid int，grade float，foreign key（sid） references stuinfo（sid），foreign key（cid） references course（cid））；

如果需要自己給約束起名字，可以加constraint關(guān)鍵字，具體SQL語(yǔ)句如下：

Create table score（sid int，cid int，grade float，constraint fk_sid foreign key（sid） references stuinfo（sid），constraint fk_cid foreign key（cid） references course（cid））；

建立外鍵約束是為了保證數(shù)據(jù)的完整和統(tǒng)一性，但如果主表中的數(shù)據(jù)被刪除或修改，從表中對(duì)應(yīng)的數(shù)據(jù)也應(yīng)該被刪除或修改，否則數(shù)據(jù)庫(kù)中會(huì)存在很多無(wú)意義的垃圾數(shù)據(jù)。MySQL可以在建立外鍵約束時(shí)添加on delete或on update子句來(lái)告訴數(shù)據(jù)庫(kù)怎樣避免垃圾數(shù)據(jù)的產(chǎn)生。從表在建立外鍵約束時(shí)可以加上on delete{cascade|set null|no action|restrict}去限制當(dāng)主表中的數(shù)據(jù)在執(zhí)行刪除或更新操作時(shí)，從表中數(shù)據(jù)做出的響應(yīng)，從而確保數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和完整性。

1.4 非空約束

非空約束指的是字段的值不能為NULL。在MySQL中，非空約束是通過(guò)not null來(lái)定義的。

Create table test（id int not null，name varchar（4） not null，age int）；

1.5 默認(rèn)值約束

默認(rèn)約束用于對(duì)數(shù)據(jù)表中的字段指定默認(rèn)值，即當(dāng)在表中插入新記錄時(shí)，如果沒(méi)有給這個(gè)賦值，那么數(shù)據(jù)庫(kù)系統(tǒng)會(huì)自動(dòng)為這個(gè)字段插入默認(rèn)值，默認(rèn)值通過(guò)default關(guān)鍵字來(lái)定義。

Create table stu（id int，name varchar（4），sex char（1） default '男'）；

2 修改表時(shí)添加約束

2.1 主鍵約束

創(chuàng)建一個(gè)基本表：create table stu（sid int，sname varchar（10））；修改表時(shí)添加主鍵約束的語(yǔ)法：alter table table_name add primary key（columns_list）；

單字段做主鍵，例如：Alter table stu add primary key（sid）；

多字段做主鍵，例如：alter table score add primary key（sid，cid）；如果想給約束起名字，那么可以使用alter table score add constraint pk_sidcid primary key（sid，cid）。

2.2 唯一鍵約束

創(chuàng)建一個(gè)基本表：create table t1（id int primary key，stuid int，stutel int）；修改表時(shí)添加唯一性約束的語(yǔ)法：alter table table_name add unique（column_name）；例如：alter table t1 add unique（stutel）。

2.3外鍵約束

創(chuàng)建表score：create table score（sid int，cid int，grade float）；添加外鍵約束的語(yǔ)法：alter table table_name add foreign key（column_name） references table_name（column_name）；

Alter table score add foreign key（sid） references stuinfo（sid）；

Alter table score add foreign key（cid） references course（cid）；

通過(guò)上面的SQL語(yǔ)句就可以實(shí)現(xiàn)建立外鍵約束，但是外鍵名稱(chēng)是列名或者一個(gè)其他生成的名稱(chēng)，需要用show create table table_name 來(lái)查看。一般外鍵名是：tablename_ibfk_number，如score_ibfk_1、score_ibfk_2等。如果想要給約束起名字，那么使用alter table score add constraint fk_sid foreign key（sid） references stuinfo（sid）。

注意：

（1）創(chuàng)建外鍵時(shí)，主表和子表中關(guān)聯(lián)的列的數(shù)據(jù)大類(lèi)型必須一致，不能一個(gè)是整形，一個(gè)是字符型，如果都是字符型，數(shù)據(jù)類(lèi)型長(zhǎng)度不一樣，也可以創(chuàng)建外鍵約束，但是如果主表中被引用的列的數(shù)據(jù)類(lèi)型長(zhǎng)度長(zhǎng)，子表中的引用列的數(shù)據(jù)類(lèi)型長(zhǎng)度短的話(huà)，那么子表中一條記錄也插不進(jìn)去。

（2）主表中被引用的列必須設(shè)置為主鍵或唯一鍵，否則子表在創(chuàng)建外鍵時(shí)將提示Cannot add foreign key constraint。

（3）創(chuàng)建外鍵約束時(shí)，會(huì)自動(dòng)根據(jù)該列創(chuàng)建一個(gè)普通索引。

2.4 非空約束

修改表時(shí)添加非空約束，使用alter table table_name modify column_name datatype not null；語(yǔ)句即可。例如：alter table test modify age int not null。

2.5 默認(rèn)值

修改表時(shí)添加默認(rèn)值約束，可以使用alter table table_name modify column_name datatype default value；語(yǔ)句。例如alter table stu modify gender char（1） default '女'。

3 結(jié)束語(yǔ)

表的約束是定義關(guān)于列中允許值的規(guī)則，強(qiáng)制完整性的標(biāo)準(zhǔn)機(jī)制，是為了防止非法的數(shù)據(jù)錄入，減少數(shù)據(jù)庫(kù)的錯(cuò)誤，使得維護(hù)更方便。

參考文獻(xiàn)：

[1]Baron Scbwartz.高性能MySQL[M].電子工業(yè)出版社，2018.

[2]唐漢明.深入淺出MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)、優(yōu)化與管理維護(hù)[M].人民郵電出版社，2014.