这篇文章给大家分享的是有关MySQL中常见的约束类型是什么的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
屏山网站建设公司成都创新互联公司,屏山网站设计制作,有大型网站制作公司丰富经验。已为屏山上千余家提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱,请找那个售后服务好的屏山做网站的公司定做!
约束的字面意思就是规定或者限制某个事该如何去做,在MySQL中,约束就是对数据表中数据指定规则,也就是对数据进行限制,以此来确保可靠性,比如不允许某列出现Null值,实际中我们会遇到以下类型的约束。
NOT NULL
: 确保列不能有NULL值
CHECK
: 确保列中的值满足特定条件
UNIQUE
: 确保一列中的所有值都不同
PRIMARY KEY
: NOT NULL
和UNIQUE
组合,唯一标识表中的每一行
FOREIGN KEY
: 外键约束
DEFAULT
: 如果未指定值,则为列设置默认值
MySQL中通过使用NOT NULL
确保列中不会出现Null值,创建表时候格式如下:
mysql> create table user(name varchar(255)not null); Query OK, 0 rows affected (0.06 sec)
如果试图插入一个null值,则会抛出异常。
mysql> insert user values(null); ERROR 1048 (23000): Column 'name' cannot be null
或者在现有表上新增NOT NULL
约束。
mysql> alter table user modify name varchar(255) not null; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
删除NOT NULL
约束。
mysql> alter table user modify name varchar(255) null; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
如果想在列上定义条件约束,可以使用CHECK,比如下面,强制让年龄字段大于18,小于80,否则将会报错。
mysql> create table user(age int(11) check(age>18 and age <80)); Query OK, 0 rows affected, 1 warning (0.06 sec)
插入测试,可以发现9、81在插入的时候抛出异常。
mysql> insert user values(9); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values(19); Query OK, 1 row affected (0.01 sec) mysql> insert user values(81); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql>
也可以进行多列约束,如年龄必须大于18,城市必须为中国。
mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city='中国')); Query OK, 0 rows affected, 1 warning (0.05 sec)
插入测试。
mysql> insert user values(81,'2'); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values(8,'2'); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values(20,'2'); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values(20,'中国'); Query OK, 1 row affected (0.01 sec) mysql> insert user values(20,'中国1'); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values(85,'中国'); Query OK, 1 row affected (0.01 sec) mysql> insert user values(9,'中国'); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.
还可以让列值必须在指定集合中,如性别必须在男、女、未知、人妖集合中。
mysql> create table user(sex varchar(255) check (sex in ('男','女','未知','人妖'))); Query OK, 0 rows affected (0.05 sec)
插入测试。
mysql> insert user values("男"); Query OK, 1 row affected (0.02 sec) mysql> insert user values("男男"); ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated. mysql> insert user values("女"); Query OK, 1 row affected (0.01 sec) mysql> insert user values("人妖"); Query OK, 1 row affected (0.00 sec)
为约束命名并删除约束。
mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18)); Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> insert user values(5); ERROR 3819 (HY000): Check constraint 'CHK_AGE' is violated. mysql> alter table user drop check CHK_AGE; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> insert user values(5); Query OK, 1 row affected (0.01 sec)
但是,这样的写法你见过吗?
猜猜下面的作用是什么。
这其实是一个case when条件判断,让其仅仅可以插入>=18,或者是在0-10之间的数。
CREATE TABLE `user` (`age` int(11) CHECK (((case when (`age` >=18) then 1 else (case when age<10 and age >0 then 1 else 2 end) end) =1)));
UNIQUE
约束确保列中的没有重复的值,UNIQUE
和 PRIMARY KEY
约束都为一列值的唯一性提供保障,但是UNIQUE
每个表可以出现多次,而PRIMARY KEY
只能出现一个。
如下面name字段不能重复。
mysql> create table user (name varchar(255),unique(name)); Query OK, 0 rows affected (0.07 sec)
插入测试。
mysql> insert user values("张三"); Query OK, 1 row affected (0.02 sec) mysql> insert user values("张三"); ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name'mysql>
对此约束进行起名,并删除。
mysql> create table user (name varchar(255),constraint name_un unique(name)); Query OK, 0 rows affected (0.07 sec) mysql> insert user values("张三"); Query OK, 1 row affected (0.02 sec) mysql> insert user values("张三"); ERROR 1062 (23000): Duplicate entry '张三' for key 'user.name_un' mysql> alter table user drop index name_un; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert user values("张三"); Query OK, 1 row affected (0.02 sec)
插入后可以用以下语句查看创建语句。
mysql> show create table user; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `name` varchar(255) DEFAULT NULL, UNIQUE KEY `name_un` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
要删除UNIQUE
约束,可以使用DROP INDEX
或ALTER TABLE
语句:
mysql> DROP INDEX name_un ON user; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table user; +-------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
在现有表上添加。
mysql> alter table user add constraint name_un unique(name); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
通常每个表中包含一个用于唯一标识每一行的值,这个列就被称为PRIMARY KEY。
mysql> create table user (id int(11) ,age int(11),primary key (id)); Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> insert user values(1,2); Query OK, 1 row affected (0.02 sec) mysql> insert user values(1,2); ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'mysql>
FOREIGN KEY
用于约束表中的一个字段必须是另一个表中某个字段所存在的值,但是在另一个表中,这个列不一定是主键,但必须是唯一性索引,否则会创建失败。
比如orders表中的userId必须参考user表中的id,如果插入的userId在user表中不存在,则无法插入。
mysql> create table orders (id int(11) primary key ,userId int(11) , FOREIGN KEY (userId) REFERENCES user(id) ); Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> insert orders values(1,3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)) mysql> insert orders values(1,1); Query OK, 1 row affected (0.01 sec)
但是存在一个问题,如果主表(user)中记录被删除或者更新,那orders中的记录该怎么办?,如下面的例子,可以发现直接报错了。
mysql> update user set id =2 where id =1; Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
MySQL提供了几个约束可以帮助我们解决这类问题,比如在user表更新时,orders也相继更新。
RESTRICT:如果子表中有记录,则拒绝更新或删除父表中的记录。
CASCADE:更新或删除父表中的记录时,自动更新或删除子表中的记录。
SET NULL:在更新或删除父表记录时,将子表中字段的值设置为空。
可以发现,默认采用的是RESTRICT,下面来修改一下,让在更新时候也同样更新,在删除时候设置null。
mysql> alter table orders add constraint orders_ibfk_1 FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on delete set null; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
测试更新
mysql> select * from user; +----+--------+ | id | name | +----+--------+ | 1 | 张三 | +----+--------+ 1 row in set (0.00 sec) mysql> select * from orders; Empty set (0.00 sec) mysql> insert orders values (1,1); Query OK, 1 row affected (0.01 sec) mysql> update user set id =2 where id =1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from orders; +----+--------+ | id | userId | +----+--------+ | 1 | 2 | +----+--------+ 1 row in set (0.01 sec)
测试删除。
mysql> delete from user where id =2; Query OK, 1 row affected (0.02 sec) mysql> select * from orders; +----+--------+ | id | userId | +----+--------+ | 1 | NULL | +----+--------+ 1 row in set (0.00 sec)
DEFAULT
约束用于为列设置默认值,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值,没有赋值指的是在insert插入数据时没有指明这个字段,如果指定null值,最终存放的还是null值。
mysql> create table user(age int(11) default 18); Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> insert user values(); Query OK, 1 row affected (0.02 sec) mysql> select * from user; +------+ | age | +------+ | 18 | +------+ 1 row in set (0.00 sec)
感谢各位的阅读!关于“MySQL中常见的约束类型是什么”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!