基于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.