Mysql约束

2022-01-15 本文已影响0人无昵称啊

一、约束基本概念

约束：指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的有效性和一致性。

主要存在的6种约束：

1、主键约束

主键约束（Primary Key Constraint）是使用最频繁的约束，一般要求每个表中设置一个主键，主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。
1）列级用法：

<字段名> <数据类型> PRIMARY KEY [AUTO_INCREMENT]

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。

默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

表中存在AUTO_INCREMENT属性的列后，才能修改AUTO_INCREMENT初始值

ALTER TABLE <表名> AUTO_INCREMENT=<初始值>

插入数据时出现唯一键冲突或手动设置了自增字段的值，则会出现，自增字段值不连续的情况。
2）表级用法：
1个表只可拥有1个主键，主键约束名固定为PRIMARY，自定义约束名不会生效

PRIMARY KEY (字段1,...)

3）修改表时添加主键约束

#列级约束
ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型> PRIMARY KEY [AUTO_INCREMENT];
#表级约束
ALTER TABLE <表名> ADD PRIMARY KEY (<字段名>);

4）修改表时删除主键约束
不支持MODIFY COLUMN删除主键约束

ALTER TABLE <表名> DROP PRIMARY KEY;
OR
ALTER TABLE <表名> DROP CONSTRAINT `PRIMARY`;

2、唯一约束

唯一约束（Unique Key Constraint）是指所有记录中字段的值不能重复出现。
1）列级用法

<字段名> <数据类型> UNIQUE

2）表级用法

[CONSTRAINT <约束名>] UNIQUE(<列名1>,...);

3）修改表时添加唯一约束

#列级约束
ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型> UNIQUE;
#表级约束
ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] UNIQUE (<字段名>);

4）修改表时删除唯一约束
不支持MODIFY COLUMN删除唯一约束

ALTER TABLE <表名> DROP INDEX <约束名>

尽管设置了约束名，实际上无法通过ALTER TABLE <表名> DROP CONSTRAINT <约束名>来删除唯一约束。

唯一约束：保证在一个字段或者一组字段里的数据都与表中其它行的对应数据不同。
唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。

唯一约束和唯一索引功能相似，但约束是为了保证数据的完整性，索引是为了辅助查询，在理论上，不一样，在实际使用时，基本没有区别。创建唯一约束时，会自动创建唯一索引。

唯一约束是通过唯一索引实现的，为了保证没有重复值，在插入新记录时会再检索一遍，为了提高检索效率，在创建唯一约束的时候就创建了唯一索引。

3、外键约束

外键约束（Foreign Key Constraint）经常和主键约束一起使用，用来确保数据的一致性。
外键约束在列级不生效
1）表级用法：

[CONSTRAINT <约束名>] FOREIGN KEY (字段名1[,字段名2,...]) REFERENCES <主表名>(列1 [,列2,...])

2）修改表时添加外键约束
不支持MODIFY COLUMN添加外键约束

ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] FOREIGN KEY (字段名1[,字段名2,...]) REFERENCES <主表名>(列1 [,列2,...])

3）修改表时删除外键约束
不支持MODIFY COLUMN删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <约束名>;
OR
ALTER TABLE <表名> DROP CONSTRAINT <约束名>;
ALTER TABLE <表名> DROP INDEX <约束名>;

4、检查约束

检查约束（Check Constraint）是用来检查数据表中字段值有效性的一种手段，能够减少无效数据的输入。
1）列级用法
虽然支持列级用法，但实际是表级约束

<字段名> <数据类型> CHECK(<检查约束>)

2）表级用法

[CONSTRAINT <约束名>] CHECK(<检查约束>);

3）修改表时添加检查约束
不支持MODIFY COLUMN添加检查约束

ALTER TABLE <表名> ADD [CONSTRAINT <约束名>] CHECK(<检查约束>);

4）修改表时删除检查约束
不支持MODIFY COLUMN删除检查约束

ALTER TABLE <表名> DROP CONSTRAINT <约束名>;

5、非空约束

非空约束（Not Null Constraint）指字段的值不能为空。
1）列级用法

<字段名> <数据类型> NOT NULL;

不支持表级用法
2）修改表时添加非空约束

ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型> NOT NULL;

3）修改表时删除非空约束

ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型>;
OR
ALTER TABLE <表名>  DROP CONSTRAINT <约束名>;

6、默认值约束

默认值约束（Default Constraint），用来指定某列的默认值，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。
1）列级用法

<字段名> <数据类型> DEFAULT <默认值>;

不支持表级用法
2）修改表时添加非空约束

ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型> DEFAULT <默认值>;

3）修改表时删除非空约束

ALTER TABLE <表名> MODIFY COLUMN <字段名> <数据类型>;
OR
ALTER TABLE <表名>  DROP CONSTRAINT <约束名>;

查看当前表使用的约束

SHOW CREATE TABLE <表名>;

更新约束必须先DROP再ADD，不支持直接更新

二、外键约束支持级联删除与更新

通过ON DELETE CASCADE ON UPDATE CASCADE;可实现级联删除与更新

[CONSTRAINT <约束名>] FOREIGN KEY (字段名1[,字段名2,...]) REFERENCES <主表名>(列1 [,列2,...]) ON DELETE CASCADE ON UPDATE CASCADE;

当存储引擎为InnoDB时，外键约束条件有以下4种：
（1）restrict方式：同no action，都是立即检查外键约束；
（2）cascade方式：在父表上update/delete记录时，同步update/delete子表的匹配记录；
（3）No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作；
（4）set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null 。