MySQL中常见的约束类型是什么

2025-03-18 技术教程

这篇文章给大家分享的是有关MySQL中常见的约束类型是什么的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

约束的字面意思就是规定或者限制某个事该如何去做，在MySQL中，约束就是对数据表中数据指定规则，也就是对数据进行限制，以此来确保可靠性，比如不允许某列出现Null值，实际中我们会遇到以下类型的约束。

NOT NULL ：确保列不能有NULL值

CHECK ：确保列中的值满足特定条件

UNIQUE ：确保一列中的所有值都不同

PRIMARY KEY： NOT NULL和UNIQUE组合，唯一标识表中的每一行

FOREIGN KEY ：外键约束

DEFAULT ：如果未指定值，则为列设置默认值

约束1.NULL

MySQL中通过使用NOT NULL确保列中不会出现Null值，创建表时候格式如下：

mysql>createtableuser(namevarchar(255)notnull);QueryOK,0rowsaffected(0.06sec)

如果试图插入一个null值，则会抛出异常。

mysql>insertuservalues(null);ERROR1048(23000):Column'name'cannotbenull

或者在现有表上新增NOT NULL约束。

mysql>altertableusermodifynamevarchar(255)notnull;QueryOK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0

删除NOT NULL约束。

mysql>altertableusermodifynamevarchar(255)null;QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:02.CHECK

如果想在列上定义条件约束，可以使用CHECK，比如下面，强制让年龄字段大于18，小于80，否则将会报错。

mysql>createtableuser(ageint(11)check(age>18andage<80));QueryOK,0rowsaffected,1warning(0.06sec)

插入测试，可以发现9、81在插入的时候抛出异常。

mysql>insertuservalues(9);ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(19);QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(81);ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>

也可以进行多列约束，如年龄必须大于18，城市必须为中国。

mysql>createtableuser(ageint(11),cityvarchar(255),check(age>18andcity='中国'));QueryOK,0rowsaffected,1warning(0.05sec)

插入测试。

mysql>insertuservalues(81,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(8,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(20,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(20,'中国');QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(20,'中国1');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(85,'中国');QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(9,'中国');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.

还可以让列值必须在指定集合中，如性别必须在男、女、未知、人妖集合中。

mysql>createtableuser(sexvarchar(255)check(sexin('男','女','未知','人妖')));QueryOK,0rowsaffected(0.05sec)

插入测试。

mysql>insertuservalues("男");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("男男");ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues("女");QueryOK,1rowaffected(0.01sec)mysql>insertuservalues("人妖");QueryOK,1rowaffected(0.00sec)

为约束命名并删除约束。

mysql>createtableuser(ageint(11),constraintCHK_AGEcheck(age>18));QueryOK,0rowsaffected,1warning(0.05sec)mysql>insertuservalues(5);ERROR3819(HY000):Checkconstraint'CHK_AGE'isviolated.mysql>altertableuserdropcheckCHK_AGE;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>insertuservalues(5);QueryOK,1rowaffected(0.01sec)

但是，这样的写法你见过吗？

猜猜下面的作用是什么。

这其实是一个case when条件判断，让其仅仅可以插入>=18，或者是在0-10之间的数。

CREATETABLE`user`(`age`int(11)CHECK(((casewhen(`age`>=18)then1else(casewhenage<10andage>0then1else2end)end)=1)));3.UNIQUE

UNIQUE约束确保列中的没有重复的值，UNIQUE和 PRIMARY KEY约束都为一列值的唯一性提供保障，但是UNIQUE每个表可以出现多次，而PRIMARY KEY只能出现一个。

如下面name字段不能重复。

mysql>createtableuser(namevarchar(255),unique(name));QueryOK,0rowsaffected(0.07sec)

插入测试。

mysql>insertuservalues("张三");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("张三");ERROR1062(23000):Duplicateentry'张三'forkey'user.name'mysql>

对此约束进行起名，并删除。

mysql>createtableuser(namevarchar(255),constraintname_ununique(name));QueryOK,0rowsaffected(0.07sec)mysql>insertuservalues("张三");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("张三");ERROR1062(23000):Duplicateentry'张三'forkey'user.name_un'mysql>altertableuserdropindexname_un;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>insertuservalues("张三");QueryOK,1rowaffected(0.02sec)

插入后可以用以下语句查看创建语句。

mysql>showcreatetableuser;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+|user|CREATETABLE`user`(`name`varchar(255)DEFAULTNULL,UNIQUEKEY`name_un`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

要删除UNIQUE约束，可以使用DROP INDEX或ALTER TABLE语句：

mysql>DROPINDEXname_unONuser;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>showcreatetableuser;+-------+-----------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+-----------------------------------------------------------------------------------------------------------------------------+|user|CREATETABLE`user`(`name`varchar(255)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+-----------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

在现有表上添加。

mysql>altertableuseraddconstraintname_ununique(name);QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:04.PRIMARY KEY

通常每个表中包含一个用于唯一标识每一行的值，这个列就被称为PRIMARY KEY。

mysql>createtableuser(idint(11),ageint(11),primarykey(id));QueryOK,0rowsaffected,2warnings(0.06sec)mysql>insertuservalues(1,2);QueryOK,1rowaffected(0.02sec)mysql>insertuservalues(1,2);ERROR1062(23000):Duplicateentry'1'forkey'user.PRIMARY'mysql>5.FOREIGN KEY

FOREIGN KEY用于约束表中的一个字段必须是另一个表中某个字段所存在的值，但是在另一个表中，这个列不一定是主键，但必须是唯一性索引，否则会创建失败。

比如orders表中的userId必须参考user表中的id，如果插入的userId在user表中不存在，则无法插入。

mysql>createtableorders(idint(11)primarykey,userIdint(11),FOREIGNKEY(userId)REFERENCESuser(id));QueryOK,0rowsaffected,2warnings(0.06sec)mysql>insertordersvalues(1,3);ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`t`.`orders`,CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`))mysql>insertordersvalues(1,1);QueryOK,1rowaffected(0.01sec)

但是存在一个问题，如果主表(user)中记录被删除或者更新，那orders中的记录该怎么办？，如下面的例子，可以发现直接报错了。

mysql>updateusersetid=2whereid=1;Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`t`.`orders`,CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`)

MySQL提供了几个约束可以帮助我们解决这类问题，比如在user表更新时，orders也相继更新。

RESTRICT：如果子表中有记录，则拒绝更新或删除父表中的记录。

CASCADE：更新或删除父表中的记录时，自动更新或删除子表中的记录。

SET NULL：在更新或删除父表记录时，将子表中字段的值设置为空。

可以发现，默认采用的是RESTRICT，下面来修改一下，让在更新时候也同样更新，在删除时候设置null。

mysql>altertableordersaddconstraintorders_ibfk_1FOREIGNKEY(`userId`)REFERENCES`user`(`id`)onupdatecascadeondeletesetnull;QueryOK,0rowsaffected(0.12sec)Records:0Duplicates:0Warnings:0

测试更新

mysql>select*fromuser;+----+--------+|id|name|+----+--------+|1|张三|+----+--------+1rowinset(0.00sec)mysql>select*fromorders;Emptyset(0.00sec)mysql>insertordersvalues(1,1);QueryOK,1rowaffected(0.01sec)mysql>updateusersetid=2whereid=1;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>select*fromorders;+----+--------+|id|userId|+----+--------+|1|2|+----+--------+1rowinset(0.01sec)

测试删除。

mysql>deletefromuserwhereid=2;QueryOK,1rowaffected(0.02sec)mysql>select*fromorders;+----+--------+|id|userId|+----+--------+|1|NULL|+----+--------+1rowinset(0.00sec)6.DEFAULT

DEFAULT约束用于为列设置默认值，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值，没有赋值指的是在insert插入数据时没有指明这个字段，如果指定null值，最终存放的还是null值。

mysql>createtableuser(ageint(11)default18);QueryOK,0rowsaffected,1warning(0.05sec)mysql>insertuservalues();QueryOK,1rowaffected(0.02sec)mysql>select*fromuser;+------+|age|+------+|18|+------+1rowinset(0.00sec)

感谢各位的阅读！关于“MySQL中常见的约束类型是什么”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！