MySQL笔记-数据库约束条件

一、数据库约束

数据库的DDL 语句仅仅指定了一些列定义，这仅是数据表的基本功能。为了数据的完整性，统一性，数据库存在许多约束。

大部分数据库支持的5中完整性约束：

1、NOT NULL：非空约束。

2、UNIQUE：唯一约束。

3、PRIMARY KEY：主键，唯一标识该条记录，相当于id。

4、FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录。

5、CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

注：MySQL不支持CHECK约束，虽然仍可使用，但无任何作用（对5.6版mysql，更高版本未知）。

为数据表指定约束有如下两个时机：

1、建表同时为相应的数据列指定约束。

2、建表后创建，以修改表的方式增加约束。

注：MySQL使用information_schema数据库里的TABLE_CONSTRAINTS表保存该数据库实例中的所有约束信息，用户可查询此表查看约束信息。

二、5种约束简介

1、NOT NULL

指定此约束：

1）、建表时在列后面加not null即可；

2）、alter table时增加或删除（列后加null）非空约束，如：

alter table tableName modify columName1 datatype [default value] not null/null;

2、UNIQUE

UNIQUE约束的列不可出现重复值，但可出现多个NULL 值（因为数据库中NULL不等于NULL）。 

指定此约束：

1）、建表时在datatype后加unique即可，适用于单列增加此约束。

2）、建表时通过表级约束语法增加此约束，适用于单列或多列同时添加此约束。语法：

constraint [约束名] unique（添加此约束的列名，多列用逗号隔开）;

例：

create table myTable（

name varchar（10） ,

phoneNum int ,

#使用表级约束语法建立约束，指定两列组合不允许重复，且指定约束名为mytable_unique。

constraint mytable_unique unique(name,phoneNum)

);

3）、alter修改表语句+add增加unique约束。例：

#为表info中的name、number列增加此约束

alter table info add unique（name，number）；

4）、alter修改表语句+modify增加unique约束。例：

alter table info modify addr varchar（40） unique;

3、PRIMARY KEY

增加此约束方法与unique相似，将unique换成primary key即可。

primary key 与unique约束相似，但primary key约束的列不能为NULL。

说明：1）、当创建主键约束时，MySQL在主键约束所在列或列组合上建立对应的唯一索引。

2）、主键约束可以是单列主键约束，也可以是列组合主键约束，但同一个表中只能有一个主键约束（组合主键约束也只算一个主键约束，关键在于标识记录）。

3）、如果某数据列类型是整形，且作为主键列，则可指定自增长。只需在primary key前加auto_increment。

删除主键约束，使用drop，如：

alter table xxx drop primary key；

（注意：实践发现modify可以增加primary key约束，但无法去除此约束！）

4、FOREIGN KEY

*外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。

外键确保了相关的两个字段的参照关系：

1）、子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空（也可通过非空约束不为空）。

2）、当主表的记录被从表参照时，主表记录不允许被删除，必须先把从表的相关参照记录全部删除后，方可删除主表的该记录。删除主表记录时，级联删除从表中相关的参照记录。

外键定义了两个实体之间一对多、一对一的关联关系。

*建立外键约束语法：

1）、列级约束语法（创建或修改表时单列内增加约束的语法，如：name int references table1（id）），但在MySQL中没实际作用，仅为了兼容SQL标准。

2）、表级约束语法：

方式一：创建表时指定外键约束：

create talbe student_table（

student_id int auto_increment primary key,

java_teacher int,

#指定java_teacher列为外键，外键约束名为student_teacher_fk，java_teacher参照主表teacher_table的teacher_id列。

constraint student_teacher_fk foreign key(java_teacher) referrences teacher_table(teacher_id)

);

说明：若不需指定外键约束名，则将foreign key之前的去掉即可。此时MySQL会自动为该外键约束命名为table_name_ibfk_n，其中table_name是从表的表名，此处即为student_table，而n是从1开始的整数。外键名可用于drop删除外键约束，同理主键名也如此。

方式二：alter+add方式：

alter table student_table

add foreign key（java_teacher） references teacher_table(teacher_id);

此外，可建立多列组合的外键约束，此时必须使用表级约束语法。外键约束也可以是参照自身，场景如经理与与普通员工同为员工，处在一个表中，但经理与员工之间又存在一对多的关系，所以此时应该参照自身，语法相同。

*定义级联删除：

在外键约束后+on delete cascade，如：foreign ……on delete cascade;

5、CHECK

建表时指定check(布尔表达式)即可，但MySQL中check不会有任何效果(mysql5.6以上未知)。

例：

create table test（

number int，

#检查number是否大于0，不满足是插入记录会失败

chech（number>0）

);

注：对于MySQL支持不太好的check约束甚至更复杂的约束，可借助MySQL 触发器机制（trigger）实现，后面学。

删除某些数据记录后主键列数组不连续，如何重新排序？

方法一：删除主键列并重设

删除主键列——>add主键列并指定auto_increment——>modify主键列的位置

说明：1、mysql中只有定义为键(key)的列可以auto_increment；2、若已有数据记录，则添加主键时，主键列数据不能相同且不为空；3、主键列的自增效果是在设置时才开始发挥作用，即对前面已有的数值型数据，并不会重新排列（即若在截图示例中，若直接删除原来的主键再指定id列为auto_increment的主键，124并不会变成123）。

综合结论：重新排序时，直接删除整个主键列，之后重新添加自增的主键列，再通过modify修改主键列位置。

方法二：