MySQL数据库常见七大约束总结#primary key、unique key、foreign key、not null、default、check、auto_increment【sql练手】

最新推荐文章于 2024-03-09 10:00:00 发布

CRUD_CODER_LIN

最新推荐文章于 2024-03-09 10:00:00 发布

阅读量2.1k

点赞数 4

分类专栏： MySQL

本文链接：https://blog.csdn.net/LINHONG_1994/article/details/98675949

版权

MySQL 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限于数据库，约束可以被当于数据库对象来处理，它们具有名称和关联模式。数据库中的约束是逻辑约束，不会因设置约束而额外的占用空间。

约束（constraints）分类(5大类)

①键约束：主键约束、外键约束、唯一键约束

②not null约束：非空约束

③check约束：检查约束

④default约束：默认值约束

⑤auto_increment约束：自增约束

1.主键约束（primary key）

基本特点：

①主键约束的列的值要求：非空、唯一、整数类型

②一个表有且只有一个主键约束

③主键约束名就叫做primary

④如果某个列建立的主键约束，mysql会自动给这个列加索引，同样删除主键对应的索引也会删除

根据主键来查询记录，效率很高！

（1）建立主键约束

create table 表名称{

字段名数据类型 primary key,

字段名数据类型，

字段名数据类型，

…

};

① 建表时指定主键约束

正常新建表、指定主键、插入数据

mysql> use coding0110lindb;            -- 使用数据库
Database changed
mysql> create table primary_test(       -- 创建表
    -> sid int primary key,
    -> sname varchar(10)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into primary_test values(1,'林大侠');  -- 插入数据
Query OK, 1 row affected (0.01 sec)

mysql> insert into primary_test values(2,'刘猪猪');
Query OK, 1 row affected (0.00 sec)

主键是唯一、非空、有且只有一个主键约束【校验】

mysql> insert into primary_test values(1,'李大侠');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  -- 主键不能重复

mysql> insert into primary_test values(null,'coding0110lin');
ERROR 1048 (23000): Column 'sid' cannot be null             -- 主键不能为空

mysql> create table primary_test02(
    -> sid int primary key,
    -> snames varchar(20) primary key
    -> );
ERROR 1068 (42000): Multiple primary key defined     -- 一张表有且只有一个主键约束

②建表后指定主键约束

alter table 表名称 add primary key (字段列表)

mysql> create table primary_add_test(          -- 新建表（无主键）
    -> sid int(10),
    -> snames varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc primary_add_test;                  -- 查询表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(10)     | YES  |     | NULL    |       |
| snames | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
    
 mysql> alter table primary_add_test add primary key(sid);  -- 添加表主键
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc primary_add_test;                                  -- 查询表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(10)     | NO   | PRI | 0       |       |
| snames | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

（2）建立复合主键约束

create table 表名称{

字段名数据类型 ,

字段名数据类型，

字段名数据类型，

primary key(字段列表)

…

};

① 建表时指定复合主键约束

mysql> create table primarys_test(               -- 新建表
    -> sid int, -- 学号
    -> cid int, -- 课程号
    -> score int (20), -- 分数
    -> primary key(sid,cid)                     -- 指定联合主键
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc primarys_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(11) | NO   | PRI | 0       |       |
| cid   | int(11) | NO   | PRI | 0       |       |
| score | int(20) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
    
   -- 测试添加数据 
mysql> insert into  primarys_test values(1,1,90),(1,2,100),(2,1,98),(2,2,66)
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from primarys_test;
+-----+-----+-------+
| sid | cid | score |
+-----+-----+-------+
|   1 |   1 |    90 |
|   1 |   2 |   100 |
|   2 |   1 |    98 |
|   2 |   2 |    66 |
+-----+-----+-------+
4 rows in set (0.00 sec)

如果你不想使用复合主键，那么就需要单独增加一列，作为主键。

mysql>  create table primarys_test02(
    ->  no int primary key, -- 序号，无逻辑意义
    ->  sid int(8), -- 学号
    ->  cid int(8), -- 课程号
    ->  score int (20) -- 分数
    ->  );
Query OK, 0 rows affected (0.01 sec)

②建表后指定复合主键约束

alter table 表名称 add primary key(字段列表)；

mysql>  create table primarys_add_test(                     -- 创建表
    ->  sid int(8), -- 学号
    ->  cid int(8), -- 课程号
    ->  score int (20) -- 分数
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> desc primarys_add_test;                         -- 查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(8)  | YES  |     | NULL    |       |
| cid   | int(8)  | YES  |     | NULL    |       |
| score | int(20) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> alter table primarys_add_test add primary key(sid,cid);       -- 添加复合主键
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc primarys_add_test;                          -- 查看主键
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(8)  | NO   | PRI | 0       |       |
| cid   | int(8)  | NO   | PRI | 0       |       |
| score | int(20) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

（3）删除主键约束

alter table 表名称 drop primary key;

mysql> desc primarys_add_test;                     -- 查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(8)  | NO   | PRI | 0       |       |
| cid   | int(8)  | NO   | PRI | 0       |       |
| score | int(20) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table primarys_add_test drop primary key;      -- 删除主键
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc primarys_add_test;            -- 查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(8)  | NO   |     | 0       |       |
| cid   | int(8)  | NO   |     | 0       |       |
| score | int(20) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

说明：手动删除主键约束，对应的索引也会自动删除

2.唯一键约束（unique key）

基本特点：

①设置了唯一键约束的列值要求：唯一（不可重复），可以为null（无非空要求）

②一个表可以多个唯一键

③唯一键的列，mysql也会自动建索引

④唯一键约束的删除，只能通过删除对应的索引来实现

思考:唯一键约束与主键的区别是什么？

①唯一键约束可能为null,主键不能为null

②唯一键约束一个表可以有多个，主键只有一个

（1）建立唯一键约束

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 unique key,

字段名数据类型 unique key,

…

);

①建表时指定唯一键约束

mysql> use coding0110lindb;
Database changed
mysql> create table unique_key(         -- 创建表
    -> sid int primary key,
    -> sname varchar(20),
    -> cardid char(18) unique key,
    -> tel char(11) unique key
    -> );
Query OK, 0 rows affected (0.01 sec)
    
  mysql> desc unique_key;              -- 查看表结构
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| cardid | char(18)    | YES  | UNI | NULL    |       |
| tel    | char(11)    | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into unique_key values(1,'coding0110lin','6666666','12345');  -- 插入数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from unique_key;         -- 查询数据
+-----+---------------+---------+-------+
| sid | sname         | cardid  | tel   |
+-----+---------------+---------+-------+
|   1 | coding0110lin | 6666666 | 12345 |
+-----+---------------+---------+-------+
1 row in set (0.00 sec)

②建表后指定唯一键约束

alter table 表名称 add unique key(字段列表)；

mysql> create table unique_add_Test(
    -> sid int primary key,
    -> sname varchar(20),
    -> cardid char(18) ,
    -> tel char(11)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table unique_add_test add unique key(cardid,tel);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc unique_add_test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| cardid | char(18)    | YES  | MUL | NULL    |       |
| tel    | char(11)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

（2）建立复合唯一键约束

两个或多个列的组合不能重复。

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 ,

字段名数据类型 ,

unique key(字段列表)

…

);

①建表时指定复合唯一键约束

mysql> create table uniques_add_test(
    -> no int primary key, -- 序号，没有业务逻辑意义
    -> sid int,
    -> cid int,
    -> score int,
    -> unique key(sid,cid)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc uniques_add_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| no    | int(11) | NO   | PRI | NULL    |       |
| sid   | int(11) | YES  | MUL | NULL    |       |
| cid   | int(11) | YES  |     | NULL    |       |
| score | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

②建表后指定复合唯一键约束

alter table 表名称 add unique key(字段列表)；

（3）删除唯一键约束

☛alter table 表名称 drop index 索引名；

mysql> desc unique_key;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| cardid | char(18)    | YES  | UNI | NULL    |       |
| tel    | char(11)    | YES  | UNI | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table unique_key drop index tel;     -- 删除索引名
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc unique_key;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| cardid | char(18)    | YES  | UNI | NULL    |       |
| tel    | char(11)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

（4）查看索引名

☛show index from 表名；

3.非空约束（not null）

基本特点：

①设置了非空约束的列，不能为null

②一个表可以有很多的非空约束

③非空约束只针对某一列来说，和其他列无关

④非空约束不会自动增加索引的

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 not null，

字段名数据类型 not null,

…

);

（1）建表时

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 not null,

字段名数据类型 not null,

…

);

mysql> create table no
tnull_test(
    -> sid int primary key,
    -> sname varchar(20) not null,
    -> gender char not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc notnull_test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
    
mysql> insert into notnull_test values(1,'coding0110lin','男');
Query OK, 1 row affected (0.00 sec)
    
mysql> insert into notnull_test values(3,'刘猪猪',null);  --  gender char not null 测试
ERROR 1048 (23000): Column 'gender' cannot be null

（2）建表后

alter table 表名称 modify 字段名数据类型 not null;

mysql> create table notnull_test02(
    -> sid int primary key,
    -> sname varchar(20),
    -> gender char
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table notnull_test02 modify sname varchar(20) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc notnull_test02;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

（3）删除非空索引

alter table 表名称 modify 字段名数据类型；

mysql> desc notnull_test02;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table notnull_test02 modify sname varchar(20) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc notnull_test02;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4.默认值约束（default）

基本特点：

①设置了默认值的列，如果添加时，没有赋值，那么就自动赋值

②一个表中可以有很多个默认值约束

③默认值约束只针对某一个列来说，和其他列无关

④默认值约束不会自动增加索引

（1）建立默认值约束

①建表时

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 default 默认值，

字段名数据类型 default 默认值，

…

);

mysql> create table default_test(
    -> sid int primary key,
    -> sname varchar(20) not null,
    -> gender char not null default '男'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc default_test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | NO   |     | 男        |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
    
mysql> insert into default_test values(1,'coding0110lin@','女');
Query OK, 1 row affected (0.01 sec)

mysql> insert into default_test values(2,'林大侠','男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into default_test values(3,'CSDN1',default);
Query OK, 1 row affected (0.01 sec)
    
mysql> insert into default_test(sid,sname) values(5,'CSDN2');
Query OK, 1 row affected (0.00 sec)
    
 mysql> select * from default_test;
+-----+----------------+--------+
| sid | sname          | gender |
+-----+----------------+--------+
|   1 | coding0110lin@ | 女       |
|   2 | 林大侠          | 男       |
|   3 | CSDN1          | 男       |
|   5 | CSDN2          | 男       |
+-----+----------------+--------+
4 rows in set (0.00 sec)

②建表后

create table 表名称(

字段名数据类型 primary key,

字段名数据类型，

…

default 默认值(默认值列表)

);

mysql> alter table default_test02 modify gender char not null default '男';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc default_test02;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | NO   |     | 男        |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

（2）删除默认值约束

alter table 表名称 modify 字段数据类型;

mysql> alter table default_test02 modify gender char;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc default_test02;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | NO   |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5.检查约束（check）

mysql是不支持的，一般用枚举类型解决问题。

mysql是不支持的check约束

mysql> create table check_test(
    -> sid int primary key,
    -> sname varchar(20),
    -> gender char check('男' or '女')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc check_test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| sid    | int(11)     | NO   | PRI | NULL    |       |
| sname  | varchar(20) | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into check_test values(1,'coding0110lin','男');
Query OK, 1 row affected (0.01 sec)
mysql> insert into check_test values(2,'check添加测试','中');  -- 不符合需求
Query OK, 1 row affected (0.01 sec)

用枚举解决

mysql> create table check_test02(
    -> sid int primary key,
    -> sname varchar(20),
    -> gender enum('男','女')
    -> );
Query OK, 0 rows affected (0.04 sec)
 mysql> insert into check_test02 values(1,'coding0110lin','男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into check_test02 values(1,'coding0110lin','中');    -- 自动检查，不符合条件
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

6.外键约束（foreign key）

外键约束可以设置约束的级别：

①Restrict方式，默认级别

②No action方式：在mysql中表现为何Restrict方式一样

③set null 方式:对主表的约束降低了

a.当主表被引用字段的值修改了或删除了，从表对应的字段设置为null

b.set null 可以分别指定 on update set null 、 on delete set null

④cascade 方式：对主表的约束降低了

a:当主表被引用字段的值修改了，从表对应的字段值跟着修改

b:当主表被引用字段的值删除了，从表的字段的值的行就跟着删除了

c:cascade方式,可以分别指定 on update cascade 、on delete cascade

⑤set default 方式，当前mysql的默认引擎innodb不识别

mysql> -- 部门是主表
mysql> create table dept(
    -> did int primary key,
    -> dname varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)
    
mysql> -- 员工表是从表
mysql> create table emp(
    -> eid int primary key,
    -> ename varchar(20),
    -> departmentId int,
    -> foreign key(departmentId) references dept(did) on update cascade on delete set null
    -> );
Query OK, 0 rows affected (0.01 sec)
    

mysql> insert into dept values(1,'外键测试0部门'),(2,'外键测试1部门'),(3,'外键测试2部门');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp values(1,'林大侠',1),(2,'李大象',2),(3,'青大飞',3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
    
  mysql> select* from dept;
+-----+---------------+
| did | dname         |
+-----+---------------+
|   1 | 外键测试0部门   |
|   2 | 外键测试1部门   |
|   3 | 外键测试2部门   |
+-----+---------------+
3 rows in set (0.00 sec)

mysql> select * from  emp;
+-----+--------+--------------+
| eid | ename  | departmentId |
+-----+--------+--------------+
|   1 | 林大侠   |            1 |
|   2 | 李大象   |            2 |
|   3 | 青大飞   |            3 |
+-----+--------+--------------+
3 rows in set (0.00 sec)


    
mysql> -- 修改主表
mysql> update dept set did =4 where did=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from dept;
+-----+---------------+
| did | dname         |
+-----+---------------+
|   1 | 外键测试0部门             |
|   2 | 外键测试1部门             |
|   4 | 外键测试2部门             |
+-----+---------------+
3 rows in set (0.00 sec)

mysql> select * from  emp;
+-----+--------+--------------+
| eid | ename  | departmentId |
+-----+--------+--------------+
|   1 | 林大侠      |            1 |
|   2 | 李大象       |            2 |
|   3 | 青大飞       |            4 |
+-----+--------+--------------+
3 rows in set (0.00 sec)
    
 mysql> -- 删除主表的记录
mysql> delete from dept where did=1;
Query OK, 1 row affected (0.04 sec)

mysql> select* from dept;
+-----+---------------+
| did | dname         |
+-----+---------------+
|   2 | 外键测试1部门             |
|   4 | 外键测试2部门             |
+-----+---------------+
2 rows in set (0.00 sec)

mysql> select * from  emp;
+-----+--------+--------------+
| eid | ename  | departmentId |
+-----+--------+--------------+
|   1 | 林大侠      |         NULL |
|   2 | 李大象       |            2 |
|   3 | 青大飞       |            4 |
+-----+--------+--------------+

7.自增约束（auto_increment）

基本特点：

①列值必须是整数类型

②列值没有赋值，自动增长

③列值必须是键列，主键、唯一键、外键、但是一般都是主键

④一个表只能有一个自增长列

⑤设置了自增长，可以手动赋值，但是不建议这么做

（1）建表时

create table 表名称(

字段名数据类型 primary key anto_increment,

字段名数据类型，

…

)

mysql> create table auto_test(
    -> sid int auto_increment,
    -> sname varchar(20)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- "表定义不正确；只能有一个自动列，必须将其定义为键"
    
    
    mysql>  create table auto_test(
    ->  sid int primary key auto_increment,
    ->  sname varchar(20)
    ->  );
   Query OK, 0 rows affected (0.01 sec)
   ------------------------------------------------------------下面主键自增长数据测试    
mysql> insert into auto_test values(1,'林大侠');
Query OK, 1 row affected (0.00 sec)

mysql> insert into auto_test values(6,'自动增长测试');
Query OK, 1 row affected (0.00 sec)

mysql> insert into auto_test values(null,'自动null测试');
Query OK, 1 row affected (0.01 sec)

mysql> insert into auto_test values(0,'自动0值测试');
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto_test;
+-----+--------------+
| sid | sname        |
+-----+--------------+
|   1 | 林大侠            |
|   6 | 自动增长测试            |
|   7 | 自动null测试        |
|   8 | 自动0值测试          |
+-----+--------------+
4 rows in set (0.00 sec)
    
   --------------------------------------------------------极值测试 
mysql> insert into auto_test values(-1,'自动-1值测试');
Query OK, 1 row affected (0.01 sec)

mysql> select * from auto_test;
+-----+--------------+
| sid | sname        |
+-----+--------------+
|  -1 | 自动-1值测试         |
|   1 | 林大侠            |
|   6 | 自动增长测试            |
|   7 | 自动null测试        |
|   8 | 自动0值测试          |
+-----+--------------+
5 rows in set (0.00 sec)
 -----------------------------------------------------------正常测试 ，主键增长变化
mysql> insert into auto_test values(null,'自动null2测试');
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_test;
+-----+---------------+
| sid | sname         |
+-----+---------------+
|  -1 | 自动-1值测试          |
|   1 | 林大侠             |
|   6 | 自动增长测试             |
|   7 | 自动null测试         |
|   8 | 自动0值测试           |
|   9 | 自动null2测试        |
+-----+---------------+
6 rows in set (0.00 sec)

（2）建表后

mysql>  create table auto_test02(
    ->  sid int primary key,
    ->  sname varchar(20)
    ->  );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table auto_test02 modify sid int auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc auto_test02;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

（3）删除自增约束

alter table 表名称 modify 字段名数据类型;

mysql> desc auto_test02;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table auto_test02 modify sid int(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc auto_test02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   | PRI | 0       |       |
| sname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

总结

数据库常见七大约束，在建表时建表后sql语句变动不大，对DDL的语法有了进一步的理解，虽然很耗时，但是多练多敲，就会有自己的见解，对原生态的语句的编写，更检查语法功底，坚持！

#轻松一刻：

在这里插入图片描述

☝上述分享来源个人总结，如果分享对您有帮忙，希望您积极转载；如果您有不同的见解，希望您积极留言，让我们一起探讨，您的鼓励将是我前进道路上一份助力，非常感谢！我会不定时更新相关技术动态，同时我也会不断完善自己，提升技术，希望与君同成长同进步！

☞本人博客：https://coding0110lin.blog.csdn.net/ 欢迎转载，一起技术交流吧！

CRUD_CODER_LIN

关注

4
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库常见七大约束总结#primary key、unique key、foreign key、not null、default、check、auto_increment【sql练手】

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限于数据库，约束可以被当于数据库对象来处理，它们具有名称和关联模式。数据库中的约束是逻辑约束，不会因设置约束而额外的占用空间。约束（constraints）分类(5大类)①键约束：主键约束、外键约束、唯一键约束②not null约束：非空约束③check约束：检查约束④default约束：默认值约束⑤auto_in...
复制链接

扫一扫

专栏目录