这篇“mysql约束规则有哪些及怎么创建”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“mysql约束规则有哪些及怎么创建”文章吧。

简介

1、数据完整性（DataIntegrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的2、为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：实体完整性（EntityIntegrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录域完整性（DomainIntegrity）：例如：年龄范围0-120，性别范围“男/女”引用完整性（ReferentialIntegrity）：例如：员工所在部门，在部门表中要能找到这个部门用户自定义完整性（User-definedIntegrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍

定义

约束是表级的强制规定可以在创建表时规定约束（通过CREATETABLE语句），或者在表创建之后通过ALTERTABLE语句规定约束

分类

#方式1根据约束数据列的限制，约束可分为：单列约束：每个约束只约束一列多列约束：每个约束可约束多列数据#方式2根据约束的作用范围，约束可分为：列级约束：只能作用在一个列上，跟在列的定义后面表级约束：可以作用在多个列上，不与列一起，而是单独定义#方式3根据约束起的作用，约束可分为：NOTNULL非空约束，规定某个字段不能为空UNIQUE唯一约束，规定某个字段在整个表中是唯一的PRIMARYKEY主键(非空且唯一)约束FOREIGNKEY外键约束CHECK检查约束DEFAULT默认值约束

查看表拥有的约束

#information_schema数据库名（系统库）#table_constraints表名称（专门存储各个表的约束）SELECT*FROMinformation_schema.table_constraintsWHEREtable_name='表名称';非空约束

简介

限定某个字段/某列的值不允许为空所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空一个表可以有很多列都分别限定了非空空字符串''不等于NULL，0也不等于NULL

代码案例

#创建表时添加非空约束CREATETABLEtest1(idINTNOTNULL,last_nameVARCHAR(15)NOTNULL,emailVARCHAR(25),salaryDECIMAL(10,2));#添加1条数据INSERTINTOtest1(id,last_name,email,salary)VALUES(1,'Tom','tom@126.com',3400);#添加1条有null的数据，报错#错误：Column'last_name'cannotbenullINSERTINTOtest1(id,last_name,email,salary)VALUES(2,NULL,'tom1@126.com',3400);#添加1条有null的数据，报错#错误：Column'id'cannotbenullINSERTINTOtest1(id,last_name,email,salary)VALUES(NULL,'Jerry','jerry@126.com',3400);#创建表之后，如果想为某个字段添加非空约束，则需要保证该字段的值都非空INSERTINTOtest1(id,email)VALUES(2,'abc@126.com');#修改表时，添加约束ALTERTABLEtest1MODIFYemailVARCHAR(25)NOTNULL;#在ALTERTABLE时删除约束ALTERTABLEtest1MODIFYemailVARCHAR(25)NULL;唯一性约束

简介

用来限制某个字段/某列的值不能重复同一个表可以有多个唯一约束唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一唯一性约束允许列值为空在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同MySQL会给唯一约束的列上默认创建一个唯一索引

代码案例

#在CREATETABLE时添加约束CREATETABLEtest2(idINTUNIQUE,#列级约束，指定在列上last_nameVARCHAR(15),emailVARCHAR(25),salaryDECIMAL(10,2),CONSTRAINTuk_test2_emailUNIQUE(email)#表级约束，在表上某1列的约束，并自定义约束别名);#插入1条数据INSERTINTOtest2(id,last_name,email,salary)VALUES(1,'Tom','tom@126.com',4500);#如果重复了，则报错#错误：Duplicateentry'1'forkey'test2.id'INSERTINTOtest2(id,last_name,email,salary)VALUES(1,'Tom1','tom1@126.com',4600);#如果重复了，则报错#错误：Duplicateentry'tom@126.com'forkey'test2.uk_test2_email'INSERTINTOtest2(id,last_name,email,salary)VALUES(2,'Tom1','tom@126.com',4600);#可以向声明为unique的字段上添加null值INSERTINTOtest2(id,last_name,email,salary)VALUES(2,'Tom1',NULL,4600);#而且可以多次添加nullINSERTINTOtest2(id,last_name,email,salary)VALUES(3,'Tom2',NULL,4600);#创建表后，再添加唯一性约束，保证要添加唯一性约束的字段的值都不相同后，才能添加约束UPDATEtest2SETsalary=5000WHEREid=3;#在ALTERTABLE时添加约束#方式1：ALTERTABLEtest2ADDCONSTRAINTuk_test2_salUNIQUE(salary);#方式2：ALTERTABLEtest2MODIFYlast_nameVARCHAR(15)UNIQUE;#复合的唯一性约束CREATETABLEUSER(idINT,`name`VARCHAR(15),`password`VARCHAR(25),CONSTRAINTuk_user_name_pwdUNIQUE(`name`,`password`)#表级约束，这时要2个字段合起来不重复);#添加1条数据INSERTINTOUSERVALUES(1,'Tom','abc');#再次添加1条数据，发现password重复了，但name没有重复，则可以添加成功INSERTINTOUSERVALUES(1,'Tom1','abc');#删除唯一性约束#添加唯一性约束的列上也会自动创建唯一索引#删除唯一约束只能通过删除唯一索引的方式删除#删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样#如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名ALTERTABLEtest2DROPINDEXlast_name;主键约束

简介

用来唯一标识表中的一行记录一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建主键约束对应着表中的一列或者多列（复合主键）如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性

代码案例

#在CREATETABLE时添加约束#一个表中最多只能有一个主键约束。#错误写法：添加了2个主键#MultipleprimarykeydefinedCREATETABLEtest3(idINTPRIMARYKEY,#列级约束last_nameVARCHAR(15)PRIMARYKEY,salaryDECIMAL(10,2),emailVARCHAR(25));#主键约束特征：非空且唯一，用于唯一的标识表中的一条记录CREATETABLEtest4(idINTPRIMARYKEY,#列级约束last_nameVARCHAR(15),salaryDECIMAL(10,2),emailVARCHAR(25));#MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用CREATETABLEtest5(idINT,last_nameVARCHAR(15),salaryDECIMAL(10,2),emailVARCHAR(25),CONSTRAINTpk_test5_idPRIMARYKEY(id)#表级约束，没有必要起名字);#添加1条数据INSERTINTOtest4(id,last_name,salary,email)VALUES(1,'Tom',4500,'tom@126.com');#插入数据时，主键重复了，报错：Duplicateentry'1'forkey'test4.PRIMARY'INSERTINTOtest4(id,last_name,salary,email)VALUES(1,'Tom',4500,'tom@126.com');#插入数据时，主键为null，所以报错：Column'id'cannotbenullINSERTINTOtest4(id,last_name,salary,email)VALUES(NULL,'Tom',4500,'tom@126.com');#复合主键约束CREATETABLEuser1(idINT,NAMEVARCHAR(15),PASSWORDVARCHAR(25),PRIMARYKEY(NAME,PASSWORD));#如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。INSERTINTOuser1VALUES(1,'Tom','abc');#插入第2条数据，password重复了，但name没有重复INSERTINTOuser1VALUES(1,'Tom1','abc');#插入1条数据，name和password和起来重复了，所以错误：Column'name'cannotbenullINSERTINTOuser1VALUES(1,NULL,'abc');#在ALTERTABLE时添加约束CREATETABLEtest6(idINT,last_nameVARCHAR(15),salaryDECIMAL(10,2),emailVARCHAR(25));#添加主键约束ALTERTABLEtest6ADDPRIMARYKEY(id);#删除主键约束ALTERTABLEtest6DROPPRIMARYKEY;自增约束

简介

某个字段的值自增一个表最多只能有一个自增长列当需要产生唯一标识符或顺序值时，可设置自增长自增长列约束的列必须是键列（主键列，唯一键列）自增约束的列的数据类型必须是整数类型如果自增列指定了0和null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值

代码案例

#在CREATETABLE时添加CREATETABLEtest7(idINTPRIMARYKEYAUTO_INCREMENT,last_nameVARCHAR(15));#开发中，一旦主键作用的字段上声明有AUTO_INCREMENT，则我们在添加数据时，就不要给主键对应的字段去赋值了INSERTINTOtest7(last_name)VALUES('Tom');#当我们向主键（含AUTO_INCREMENT）的字段上添加0或null时，实际上会自动的往上添加指定的字段的数值INSERTINTOtest7(id,last_name)VALUES(0,'Tom');INSERTINTOtest7(id,last_name)VALUES(NULL,'Tom');#自定义id时，不会使用自定义的idINSERTINTOtest7(id,last_name)VALUES(10,'Tom');#在ALTERTABLE时添加CREATETABLEtest8(idINTPRIMARYKEY,last_nameVARCHAR(15));#添加自增约束ALTERTABLEtest8MODIFYidINTAUTO_INCREMENT;#在ALTERTABLE时删除ALTERTABLEtest8MODIFYidINT;

MySQL8.0新特性—自增变量的持久化

#在MySQL5.7中演示#创建表，并设置为自增CREATETABLEtest9(idINTPRIMARYKEYAUTO_INCREMENT);#插入4条数据INSERTINTOtest9VALUES(0),(0),(0),(0);#删除第4条数据DELETEFROMtest9WHEREid=4;#再插入1条数据，发现是从5开始自增的INSERTINTOtest9VALUES(0);#删除第5条数据DELETEFROMtest9WHEREid=5;#重启服务器#再插入1条数据，发现是从4开始自增的INSERTINTOtest9VALUES(0);#这是因为，在MySQL5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化#在MySQL8.0中演示#创建表，并设置为自增CREATETABLEtest9(idINTPRIMARYKEYAUTO_INCREMENT);#插入4条数据INSERTINTOtest9VALUES(0),(0),(0),(0);#删除第4条记录DELETEFROMtest9WHEREid=4;#再插入1条记录，发现是从5开始自增的INSERTINTOtest9VALUES(0);#删除第5条记录DELETEFROMtest9WHEREid=5;#重启服务器#再插入1条记录，发现是从6开始自增的INSERTINTOtest9VALUES(0);#这是因为，MySQL8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值外键约束

简介

限定某个表的某个字段的引用完整性主表（父表）：被引用的表，被参考的表从表（子表）：引用别人的表，参考别人的表从表的外键列，必须引用/参考主表的主键或唯一约束的列在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表删表时，先删从表（或先删除外键约束），再删除主表当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据在“从表”中指定外键约束，并且一个表可以建立多个外键约束从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR1005(HY000):Can'tcreatetable'database.tablename'(errno:150)”当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）删除外键约束后，必须手动删除对应的索引

代码案例

#先创建主表CREATETABLEdept1(dept_idINT,dept_nameVARCHAR(15));#操作报错，因为主表中的dept_id上没有主键约束或唯一性约束#再创建从表CREATETABLEemp1(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(15),department_idINT,CONSTRAINTfk_emp1_dept_idFOREIGNKEY(department_id)REFERENCESdept1(dept_id)#表级约束，指定外键);#主表添加主键约束ALTERTABLEdept1ADDPRIMARYKEY(dept_id);#再创建从表CREATETABLEemp1(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(15),department_idINT,CONSTRAINTfk_emp1_dept_idFOREIGNKEY(department_id)REFERENCESdept1(dept_id)#表级约束);#演示外键的效果#添加失败，因为外键的值在主表中不存在INSERTINTOemp1VALUES(1001,'Tom',10);#主表中添加数据INSERTINTOdept1VALUES(10,'IT');#在主表dept1中添加了10号部门以后，我们就可以在从表中添加10号部门的员工INSERTINTOemp1VALUES(1001,'Tom',10);#删除失败，从表中有该值的关联数据DELETEFROMdept1WHEREdept_id=10;#更新失败，从表中有该值的关联数据UPDATEdept1SETdept_id=20WHEREdept_id=10;#在ALTERTABLE时添加外键约束#创建表1CREATETABLEdept2(dept_idINTPRIMARYKEY,dept_nameVARCHAR(15));#创建表2CREATETABLEemp2(emp_idINTPRIMARYKEYAUTO_INCREMENT,emp_nameVARCHAR(15),department_idINT);#添加约束ALTERTABLEemp2ADDCONSTRAINTfk_emp2_dept_idFOREIGNKEY(department_id)REFERENCESdept2(dept_id);

约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录Setnull方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为notnullNoaction方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作Restrict方式：同noaction，都是立即检查外键约束Setdefault方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别如果没有指定等级，就相当于Restrict方式对于外键约束，最好是采用:ONUPDATECASCADEONDELETERESTRICT的方式。

代码案例

#创建表1CREATETABLEdept(didINTPRIMARYKEY,#部门编号dnameVARCHAR(50)#部门名称);#创建表2CREATETABLEemp(eidINTPRIMARYKEY,#员工编号enameVARCHAR(5),#员工姓名deptidINT,#员工所在的部门FOREIGNKEY(deptid)REFERENCESdept(did)ONUPDATECASCADEONDELETESETNULL#把修改操作设置为级联修改等级，把删除操作设置为setnull等级);#主表插入数据INSERTINTOdeptVALUES(1001,'教学部');INSERTINTOdeptVALUES(1002,'财务部');INSERTINTOdeptVALUES(1003,'咨询部');#从表插入数据INSERTINTOempVALUES(1,'张三',1001);#在添加这条记录时，要求部门表有1001部门INSERTINTOempVALUES(2,'李四',1001);INSERTINTOempVALUES(3,'王五',1002);#修改时级联修改UPDATEdeptSETdid=1004WHEREdid=1002;#删除时设置为nullDELETEFROMdeptWHEREdid=1004;

删除外键约束

ALTERTABLEemp1DROPFOREIGNKEYfk_emp1_dept_id;#再手动的删除外键约束对应的普通索引SHOWINDEXFROMemp1;ALTERTABLEemp1DROPINDEXfk_emp1_dept_id;

小结

在MySQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性

阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度检查约束

简介

检查某个字段的值是否符号xx要求，一般指的是值的范围MySQL5.7可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告MySQL8.0中可以使用check约束了

代码案例

#创建表，并指定约束CREATETABLEtest10(idINT,last_nameVARCHAR(15),salaryDECIMAL(10,2)CHECK(salary>2000));#添加1条数据INSERTINTOtest10VALUES(1,'Tom',2500);#添加失败INSERTINTOtest10VALUES(2,'Tom1',1500);默认约束

简介

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值

代码案例

#在CREATETABLE添加约束CREATETABLEtest11(idINT,last_nameVARCHAR(15),salaryDECIMAL(10,2)DEFAULT2000);#添加数据时有值，则不使用默认值INSERTINTOtest11(id,last_name,salary)VALUES(1,'Tom',3000);#使用默认值INSERTINTOtest11(id,last_name)VALUES(2,'Tom1');#在ALTERTABLE添加约束CREATETABLEtest12(idINT,last_nameVARCHAR(15),salaryDECIMAL(10,2));ALTERTABLEtest12MODIFYsalaryDECIMAL(8,2)DEFAULT2500;#在ALTERTABLE删除约束ALTERTABLEtest12MODIFYsalaryDECIMAL(8,2);

以上就是关于“mysql约束规则有哪些及怎么创建”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。