【MYSQL】check 检查约束（详解）

最新推荐文章于 2024-06-12 11:11:20 发布

zbossz

最新推荐文章于 2024-06-12 11:11:20 发布

阅读量7.2k

点赞数 5

分类专栏： MYSQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_54130714/article/details/123101572

版权

MySQL check约束数据限制数据完整性性别字段

关键词由CSDN通过智能技术生成

MYSQL 专栏收录该内容

13 篇文章

订阅专栏

check约束其实就是一个检查功能，反正就是对输入数据的一个限制，如果不满足限制，那么就无法插入数据。

mysql> create table gender(id int primary key auto_increment,name varchar(20),sex varchar(2),
    -> check(sex='男' or sex='女' or sex='秘密'));
Query OK, 0 rows affected (0.04 sec)

mysql> desc gender;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | varchar(2)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into gender values(null,'z1','秘密');
Query OK, 1 row affected (0.00 sec)

mysql> insert into gender values(null,'z2','女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into gender values(null,'z3','男');
Query OK, 1 row affected (0.00 sec)

mysql> select * from gender;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | z1   | 秘密 |
|  2 | z2   | 女   |
|  3 | z3   | 男   |
+----+------+------+
3 rows in set (0.00 sec)

mysql> insert into gender values(null,'z3','123');
ERROR 1406 (22001): Data too long for column 'sex' at row 1
mysql> insert into gender values(null,'z3','12');
ERROR 3819 (HY000): Check constraint 'gender_chk_1' is violated.
mysql> select * from gender;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | z1   | 秘密 |
|  2 | z2   | 女   |
|  3 | z3   | 男   |
+----+------+------+
3 rows in set (0.00 sec)

在这里插入图片描述