基于MySQL數據庫實施完整性約束的研究

王麗娟 吳東明

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

關鍵詞：完整性；主鍵；外鍵；唯一鍵；非空；默認值

中圖分類號：TP311.1 文獻標志碼：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

為了防止在數據表中插入錯誤的數據，在MySQL中，定義了一些維護數據庫完整性的規則，即數據表的約束。常用的約束有主鍵約束、外鍵約束、唯一鍵約束、非空約束和默認值約束，下面將對創建和刪除表的約束進行論述。

1 創建表的時候直接加約束

1.1 主鍵約束

主鍵約束可以唯一標識表中的記錄，通過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））；

后一種創建表時加主鍵約束可以使用constraint給約束起別名，但是只能放在最前、最后或者某個字段定義結束（create table course（cid int，constraint pk_cid primary key（cid），scname varchar（30））；）并加逗號分隔。在MySQL中給主鍵約束起約束名沒有太大意義，因為一個表只有一個主鍵，刪除主鍵約束時用不到約束的名字。

（2）多字段主鍵

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

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

1.2 唯一性約束

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

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

1.3 外鍵約束

外鍵約束是將主表的某些列和子表的某些列關聯在一起，其目的是為了不讓子表的列隨意增加主表列中不存在的項，如stuinfo表中沒有129501111這個學號，那么子表score表中就不能插入這個學號的列，主表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關鍵字，具體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））；

建立外鍵約束是為了保證數據的完整和統一性，但如果主表中的數據被刪除或修改，從表中對應的數據也應該被刪除或修改，否則數據庫中會存在很多無意義的垃圾數據。MySQL可以在建立外鍵約束時添加on delete或on update子句來告訴數據庫怎樣避免垃圾數據的產生。從表在建立外鍵約束時可以加上on delete{cascade|set null|no action|restrict}去限制當主表中的數據在執行刪除或更新操作時，從表中數據做出的響應，從而確保數據庫中數據的一致性和完整性。

1.4 非空約束

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

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

1.5 默認值約束

默認約束用于對數據表中的字段指定默認值，即當在表中插入新記錄時，如果沒有給這個賦值，那么數據庫系統會自動為這個字段插入默認值，默認值通過default關鍵字來定義。

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

2 修改表時添加約束

2.1 主鍵約束

創建一個基本表：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 唯一鍵約束

創建一個基本表：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外鍵約束

創建表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語句就可以實現建立外鍵約束，但是外鍵名稱是列名或者一個其他生成的名稱，需要用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）創建外鍵時，主表和子表中關聯的列的數據大類型必須一致，不能一個是整形，一個是字符型，如果都是字符型，數據類型長度不一樣，也可以創建外鍵約束，但是如果主表中被引用的列的數據類型長度長，子表中的引用列的數據類型長度短的話，那么子表中一條記錄也插不進去。

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

（3）創建外鍵約束時，會自動根據該列創建一個普通索引。

2.4 非空約束

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

2.5 默認值

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

3 結束語

表的約束是定義關于列中允許值的規則，強制完整性的標準機制，是為了防止非法的數據錄入，減少數據庫的錯誤，使得維護更方便。

參考文獻：

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

[2]唐漢明.深入淺出MySQL數據庫開發、優化與管理維護[M].人民郵電出版社，2014.