基于MySQL數(shù)據(jù)庫實(shí)施完整性約束的研究

王麗娟 吳東明

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

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

中圖分類號：TP311.1 文獻(xiàn)標(biāo)志碼：A 文章編號：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ù)表中插入錯誤的數(shù)據(jù)，在MySQL中，定義了一些維護(hù)數(shù)據(jù)庫完整性的規(guī)則，即數(shù)據(jù)表的約束。常用的約束有主鍵約束、外鍵約束、唯一鍵約束、非空約束和默認(rèn)值約束，下面將對創(chuàng)建和刪除表的約束進(jìn)行論述。

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

1.1 主鍵約束

主鍵約束可以唯一標(biāo)識表中的記錄，通過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)建表時加主鍵約束可以使用constraint給約束起別名，但是只能放在最前、最后或者某個字段定義結(jié)束（create table course（cid int，constraint pk_cid primary key（cid），scname varchar（30））；）并加逗號分隔。在MySQL中給主鍵約束起約束名沒有太大意義，因?yàn)橐粋€表只有一個主鍵，刪除主鍵約束時用不到約束的名字。

（2）多字段主鍵

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

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

1.2 唯一性約束

創(chuàng)建唯一性約束時，可以在字段的描述后面直接加unique，或者在定義表的任意位置（完整描述）添加unique（columnname），默認(rèn)的約束名稱是該列的名字，但是如果修改了這個列的名稱，約束的名稱是不會修改的。如果想給約束起名字，可以使用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表中沒有129501111這個學(xué)號，那么子表score表中就不能插入這個學(xué)號的列，主表course表中沒有08113216這個課程編號，子表score表中就不能插入這個編號的列。

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語句如下：

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

1.4 非空約束

非空約束指的是字段的值不能為NULL。在MySQL中，非空約束是通過not null來定義的。

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

1.5 默認(rèn)值約束

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

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

2 修改表時添加約束

2.1 主鍵約束

創(chuàng)建一個基本表：create table stu（sid int，sname varchar（10））；修改表時添加主鍵約束的語法：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)建一個基本表：create table t1（id int primary key，stuid int，stutel int）；修改表時添加唯一性約束的語法：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）；添加外鍵約束的語法：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）；

通過上面的SQL語句就可以實(shí)現(xiàn)建立外鍵約束，但是外鍵名稱是列名或者一個其他生成的名稱，需要用show create table table_name 來查看。一般外鍵名是：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)建外鍵時，主表和子表中關(guān)聯(lián)的列的數(shù)據(jù)大類型必須一致，不能一個是整形，一個是字符型，如果都是字符型，數(shù)據(jù)類型長度不一樣，也可以創(chuàng)建外鍵約束，但是如果主表中被引用的列的數(shù)據(jù)類型長度長，子表中的引用列的數(shù)據(jù)類型長度短的話，那么子表中一條記錄也插不進(jìn)去。

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

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

2.4 非空約束

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

2.5 默認(rèn)值

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

3 結(jié)束語

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

參考文獻(xiàn)：

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

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