MySQL相关约束

最新推荐文章于 2023-04-13 17:04:24 发布

丿BAIKAL巛

最新推荐文章于 2023-04-13 17:04:24 发布

阅读量242

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Keep__Me/article/details/123690614

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL相关约束

约束名称	关键字	约束描述
非空约束	not null	非空约束主要是限制字段的值不能为null
默认约束	default	mysql中如果没有为指定的字段设置数据，则默认值是null，我们也可以使用默认约束来改变其默认值
检测约束	check	检查约束一般是对表中的字段的值进行检查比较的。注意：如果mysql的版本是 8.0.15之前的，check约束会失效. 如果mysql的版本是 8.0.16之后的，check约束生效. 也可以用enum来实现和check约束一样的功能
唯一约束	unique	唯一约束一般可以限制表中字段的值永远唯一唯一的字段可以放置null.
主键约束	primary key	主键是一张表中用来区别数据的唯一的重要标志。一张表中一定要有一个主键。一般在表中使用编号作为主键。主键约束 = 非空约束 + 唯一约束主键的作用：用来唯一识别和查询
自增约束	auto_increment	自增约束就是对整数类型的主键进行自动增长1，实现唯一的效果。使用条件：主键 + 整数类型
外键约束	从表的外键： foreign key(外键) references 主表(主键)	外键就是表与表之间的关系的桥梁。外键是用来搭建表与表之间关系的。关系：一对一、一对多、多对多外键是一个表中主要是用来搭建关系的字段，不是原表中的固定字段。有主键的表是主表，有外键的表是从表从表中的外键要依赖与主表中的主键的存在而存在。创建数据时，先要创建主表中的数据，然后才能创建从表中的数据。删除数据时，要先删除从表中的外键对应的值，才能再删除主表中的对应的主键值.

使用及测试：

非空约束

 -- 创建表
 create table t1(
 id int not null,
 name varchar(20) not null
 );
-- 展示表结构，发现两个字段的Null为NO，说明非空约束设置成功
desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

默认约束

 -- 建表设置sex字段默认值为男
 create table t2(
 id int not null,
 sex char(2) not null default '男'
 );

-- 展示表结构，发现sex字段的Default为男，说明默认约束设置成功
desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | NO   |     | NULL    |       |
| sex   | char(2) | NO   |     | 男      |       |
+-------+---------+------+-----+---------+-------+

-- 插入两条数据，第一条不设置性别
insert into t2(id) values(1);
insert into t2 values(2,'女');

-- 查询表中所有数据，发现第一条sex字段数据默认值为男
select * from t2;
+----+-----+
| id | sex |
+----+-----+
|  1 | 男  |
|  2 | 女  |
+----+-----+

检查约束

 create table t3(
 id int not null,
 age int check(age>0 and age<=120)
 );


 desc t3;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

-- 插入正常数据，插入成功
insert into t3 values(1,22);

-- 插入不符合check约束的数据，则会报错
insert into t3 values(2,220);
ERROR 3819 (HY000): Check constraint 't3_chk_1' is violated.
insert into t3 values(2,-20);
ERROR 3819 (HY000): Check constraint 't3_chk_1' is violated.

案例：

约束性别列的只能是男或女

方式一：采用check约束来实现（mysql的版本是 8.0.16之后的可以用该方法来实现）

 create table t31(
 id int ,
 sex char(2) check(sex in ('男','女'))
 );

 desc t31;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int     | YES  |     | NULL    |       |
| sex   | char(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

-- 插入的sex字段数据不是'男'或'女'，报错，无法插入(check约束起了作用)
insert into t31 values(1,'不');
ERROR 3819 (HY000): Check constraint 't31_chk_1' is violated.

 insert into t31 values(3,'n');
ERROR 3819 (HY000): Check constraint 't31_chk_1' is violated.
-- 插入成功
insert into t31 values(1,'男');
Query OK, 1 row affected (0.00 sec)

insert into t31 values(2,'女');
Query OK, 1 row affected (0.01 sec)

方式二：采用enum（枚举类型）来实现[5.7版本可以用该方法来实现和check约束相同的效果]

 create table t32(
 id int ,
 sex enum('男','女')
 );

desc t32;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int             | YES  |     | NULL    |       |
| sex   | enum('男','女') | YES  |     | NULL    |       |
+-------+-----------------+------+-----+---------+-------+

-- 无法插入不符合要求的数据（约束起了作用）
 insert into t32 values(1,'不');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
 insert into t32 values(1,'n');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

-- 插入成功
 insert into t32 values(1,'男');
Query OK, 1 row affected (0.01 sec)

 insert into t32 values(2,'女');
Query OK, 1 row affected (0.00 sec)

唯一约束

用法一，在字段后面直接设置该字段唯一

 create table t41(
 id int unique,
 name varchar(20)
 );
 
 desc t41;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
-- 插入数据
insert into t41 values(1,'李四');

-- 插入的数据的id和上一条数据一样，则插入失败，唯一约束起作用
insert into t41 values(1,'王五');
ERROR 1062 (23000): Duplicate entry '1' for key 't41.id'

-- 更改插入的id的值，则插入成功
insert into t41 values(2,'王五');
Query OK, 1 row affected (0.01 sec)

用法二，在建表的最后一行可以设置多个字段联合唯一

 -- 建表并设置name和address联合唯一，即当两个字段同时重复时才算不唯一
 create table t42(
 id int ,
 name varchar(20),
 address varchar(20),
 unique(name,address)
 );
 
 desc t42;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(20) | YES  | MUL | NULL    |       |
| address | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

insert into t42 values(1,'张学友','香港');

-- name和address字段都重复，所以插入失败
insert into t42 values(2,'张学友','香港');
ERROR 1062 (23000): Duplicate entry '张学友-香港' for key 't42.name'

-- 插入成功
insert into t42 values(2,'张学友','郑州');

insert into t42 values(2,'刘德华','郑州');

主键约束

用法一：在字段的后面直接使用primary key即可

 -- 建表并设置id为主键
 create table t51(
 id int primary key,
 name varchar(20)
 );

desc t51;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


insert into t51 values(1,'李四');
-- 主键id重复，插入失败
insert into t51 values(1,'王五');
ERROR 1062 (23000): Duplicate entry '1' for key 't51.PRIMARY'
-- 主键id为空，插入失败
insert into t51 values(null,'王五');
ERROR 1048 (23000): Column 'id' cannot be null
-- 插入成功
insert into t51 values(2,'王五');
Query OK, 1 row affected (0.00 sec)

用法二：在创建语句的后面使用primary key(…)即可

 -- 建表，并设置id为主键
 create table t52(
 id varchar(20) ,
 name varchar(20),
 primary key(id)
 );

-- 建表，并设置name和address为联合主键
 create table t53(
 name varchar(20),
 sex char(2),
 address varchar(20),
 primary key(name,address)
 );


 desc t53;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | NO   | PRI | NULL    |       |
| sex     | char(2)     | YES  |     | NULL    |       |
| address | varchar(20) | NO   | PRI | NULL    |       |
+---------+-------------+------+-----+---------+-------+


insert into t53 values('张学友','男','香港');
Query OK, 1 row affected (0.01 sec)
-- name和adress和上一条数据重复，插入失败（联合主键约束生效）
insert into t53 values('张学友','女','香港');
ERROR 1062 (23000): Duplicate entry '张学友-香港' for key 't53.PRIMARY'

insert into t53 values('张学友','女','重庆');
Query OK, 1 row affected (0.01 sec)

-- 主键adress不能为空，插入失败
 insert into t53 values('张学友','女',null);
ERROR 1048 (23000): Column 'address' cannot be null
mysql>

自增约束

 create table t6(
 id int primary key auto_increment,
 name varchar(20)
 );
 
 desc t6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

-- 插入数据，设置id的值为空，自增约束每次自增1
insert into t6 values(null,'aa');

insert into t6 values(null,'bb');

insert into t6 values(null,'cc');

-- 查询数据，发现id自增，每次+1（自增约束生效）
select * from t6;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+

-- 插入数据
insert into t6 values(100,'dd');
insert into t6 values(null,'ee');

select * from t6;
+-----+------+
| id  | name |
+-----+------+
|   1 | aa   |
|   2 | bb   |
|   3 | cc   |
| 100 | dd   |
| 101 | ee   |
+-----+------+