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

最新推荐文章于 2025-05-16 15:09:29 发布
最新推荐文章于 2025-05-16 15:09:29 发布
阅读量2.4k 收藏 15
点赞数 4
CC 4.0 BY-SA版权
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/LINHONG_1994/article/details/98675949

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

约束（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,'优快云1',default);
Query OK, 1 row affected (0.01 sec)
    
mysql> insert into default_test(sid,sname) values(5,'优快云2');
Query OK, 1 row affected (0.00 sec)
    
 mysql> select * from default_test;
+-----+----------------+--------+
| sid | sname          | gender |
+-----+----------------+--------+
|   1 | coding0110lin@ | 女       |
|   2 | 林大侠          | 男       |
|   3 | 优快云1          | 男       |
|   5 | 优快云2          | 男       |
+-----+----------------+--------+
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.youkuaiyun.com/  欢迎转载，一起技术交流吧！

MySQL (十三) : MySQL约束(详细、全)、非空约束not null、唯一约束unique、primary key (唯一且非空)主键约束、default约束、check约束
Guizy
04-18 2492
第13章_约束 1. 约束(constraint)概述 1.1 为什么需要约束 数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑： 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录 域完整性（Doma
MySQL数据库—SQL汇总
热门推荐
Sirm23333
10-24 7万+
一、准备 下文整理常见SQL语句的用法，使用MySQL5.7测试，参考了尚硅谷MySQL教程及用例。用例sql： 链接: https://pan.baidu.com/s/1tb3-12MRNFjV8drFlN6wzg&shfl=sharepset 密码: fc2h 为了方便查阅可从右侧目录快速索引 二、DQL（Data Query Language）数据查询语言 1、语句顺序 书写顺序...
数据库7大约束
zhuge_long的专栏
06-20 688
约束是在符合数据类型的情况下，对数据的限制。主键约束：empno（int）：1，2，3，4，1，2，3，4。
数据库中表的常见七大约束
apt1203JN的博客
05-14 2万+
真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业 务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。 1、空属性not null：not null 此列属性不允许为空，必须做数据插入 create table t1( per_name varchar(20), per_age int no...
【MySQL】表的约束
Sweet_0115的博客
05-16 992
【MySQL】表的约束
sql约束（unique，primary key，check，default）
YaTou_BuKu的博客
12-06 971
unique unique：约束唯一标识数据库表中的每条记录 创建表时创建约束 验证一个唯一字段 unique  字段 验证多个唯一字段：Constraint  uc_personID unique(字段，字段) 创建表后创建约束： Alter table 表名 add unique （字段） 取消 alter table 表名 drop unique Alter table 表名...
SQL sever数据库七种常见的约束
qq_48572937的博客
06-15 1449
1、表识列（identity）:数据类型必须为整型 2、非空约束（not null）：列值是否允许为空 3、主键约束（primary key）：主键是能够唯一标识一条记录的键 允许为空，主键不允许又重复的值 4、唯一约束（unique）：不允许有重复的值，但可以是空值 5、检查约束（check）检查字段约束是否符合规定的规则 6、默认约束（default）为字段添加默认值 7、外键约束（foreign key）删除主键表前，先删除外键表 注：前两种只能在建表的时候使用约束。 后五种可以在..
数据库常见约束
09-03
是数据库在创建表的时候的一些常见约束，主要是主键与外键的语法规则
-- 用户表 CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL COMMENT 'bcrypt加密存储', email VARCHAR(100) NOT NULL UNIQUE, role ENUM('user', 'admin') DEFAULT 'user', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_users_username (username) ); -- 书籍表 CREATE TABLE books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, publisher VARCHAR(255), isbn VARCHAR(13) UNIQUE NOT NULL CHECK (REGEXP_LIKE(isbn, '^[0-9]{13}$')), genre VARCHAR(50), stock INT DEFAULT 1 CHECK (stock >= 0), INDEX idx_books_author (author), INDEX idx_books_title (title(191)) ) COMMENT='书籍基本信息表'; -- 推荐表 CREATE TABLE recommendations ( recommendation_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, book_id INT, book_title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, recommendation_date DATETIME DEFAULT CURRENT_TIMESTAMP, status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending', reason TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (book_id) REFERENCES books(book_id), UNIQUE KEY unique_recommendation (user_id, book_title, author), INDEX idx_recommendations_status (status) ); -- 审核表 CREATE TABLE reviews ( review_id INT AUTO_INCREMENT PRIMARY KEY, recommendation_id INT NOT NULL, admin_id INT NOT NULL, review_date DATETIME DEFAULT CURRENT_TIMESTAMP, result ENUM('approved', 'rejected') NOT NULL, comments TEXT, FOREIGN KEY (recommendation_id) REFERENCES recommendations(recommendation_id), FOREIGN KEY (admin_id) REFERENCES users(user_id) );根据代码给我提供概念结构设计，逻辑结构设计，物理结构设计，概念结构设计要求画E-R图，请按规范画图；逻辑结构设计要求给出关系模式，关系模式要求标明主外键；物理结构设计需要给出数据类型、主外键约束和其它自定义约束。
03-20
sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL, sgender ENUM('男','女') DEFAULT '男', sbirth DATE CHECK(sbirth > '1900-01-01') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE ...
优化一下以下sql CREATE TABLE public.zy_marketing_plan ( id bigserial NOT NULL, creator_id int8 NOT NULL, work_type varchar(16) NOT NULL, start_date date NOT NULL, start_date_detail varchar(2) NOT NULL, end_date date NOT NULL, end_date_detail varchar(2) NOT NULL, customer_name varchar(100) NOT NULL, is_fill_in int2 NOT NULL DEFAULT 0, interviewee varchar(100) NULL DEFAULT NULL::character varying, visit_location varchar(100) NOT NULL, describe varchar(500) NULL DEFAULT NULL::character varying, record_id int8 NULL DEFAULT NULL, check_in_id int8 NULL DEFAULT NULL, is_delete int2 NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT zy_marketing_plan_pkey PRIMARY KEY (id) ); CREATE INDEX idx_creator_id ON public.zy_marketing_plan USING btree (creator_id); CREATE INDEX idx_start_date ON public.zy_marketing_plan USING btree (start_date); CREATE INDEX idx_end_date ON public.zy_marketing_plan USING btree (end_date); CREATE INDEX idx_record_id ON public.zy_marketing_plan USING btree (record_id); CREATE INDEX idx_check_in_id ON public.zy_marketing_plan USING btree (check_in_id); CREATE TABLE public.zy_marketing_plan_relation ( id bigserial NOT NULL, plan_id int8 NOT NULL, user_id int8 NOT NULL, user_name varchar(16) NOT NULL, relation_type varchar(16) NOT NULL, is_delete int2 NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT zy_marketing_plan_relation PRIMARY KEY (id) ); CREATE INDEX idx_plan_id ON public.zy_marketing_plan_relation USING btree (plan_id); CREATE INDEX idx_user_id ON public.zy_marketing_plan_relation USING btree (user_id);
最新发布
07-23
最后，根据引用[4]，对于大表，我们还可以考虑分区和定期收缩数据库（如果使用SQL Server），但MySQL中收缩空间的操作并不常用，通常通过重建表（OPTIMIZE TABLE）来整理碎片。 请根据实际业务情况调整上述建议。...
-- 使用InnoDB引擎支持事务 CREATE DATABASE travel_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 景点表（空间索引优化位置查询） CREATE TABLE attractions ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT, location POINT NOT NULL SRID 4326, -- 空间数据类型 price DECIMAL(10, 2), capacity INT, SPATIAL INDEX(location) ) ENGINE=InnoDB; -- 酒店表（JSON类型存储动态属性） CREATE TABLE hotels ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, address VARCHAR(200), room_types JSON, -- 存储房型及价格 {'standard': 200, 'deluxe': 400} amenities JSON -- 设施 {'wifi': true, 'parking': false} ) ENGINE=InnoDB; -- 会员表（分区表优化查询） CREATE TABLE members ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password CHAR(60) NOT NULL, -- 存储bcrypt哈希 level ENUM('basic', 'silver', 'gold') DEFAULT 'basic', points INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p0 VALUES LESS THAN (2023), PARTITION p1 VALUES LESS THAN (2024) ); -- 线路表（使用全文索引） CREATE TABLE tours ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT, attractions JSON, -- 包含的景点ID [1, 3, 5] price DECIMAL(10, 2), FULLTEXT INDEX (name, description) ) ENGINE=InnoDB; -- 预定表（事务处理） CREATE TABLE bookings ( id INT AUTO_INCREMENT PRIMARY KEY, member_id INT NOT NULL, item_type ENUM('attraction', 'hotel', 'tour') NOT NULL, item_id INT NOT NULL, quantity INT NOT NULL, booking_date DATETIME NOT NULL, status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE ) ENGINE=InnoDB; 讲上述数据库设计的代码改成SQL server的代码
06-18
假设原MySQL表设计如下：CREATETABLEexample(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(100),locationGEOMETRY,attributesJSON,created_atDATE,contentTEXT,PRIMARYKEY(id),SPATIALINDEX(location),FULLTEXTINDEX...
MySQL四类约束primary key,auto_increment,unique,not null详解；
data_curd的博客
05-16 5298
primary key：主键约束 主键必须包含唯一的值 主键列不能包含null值 每个表都应该有一个主键，并且每个表只能有一个主键 创建primary key的方式有三种： 其一： create table cin_01( id int primary key, name varchar(32), address varchar(32), city varchar(32) ); 注意：如果有列类型或者列的长度需要修改，则可以用modify关键字，修改列类型或者长度 格式：alter tabl
数据库MySQL-4-MySQL约束（default、not null、unique、primary key、foreign key、auto_increment、unsigned、zerofill)
巫山♔
08-10 2364
MySQL的约束 概念 是一种限制，它是对表的行和列的数据做出约束，确保表中的数据的完整性和唯一性。 使用场景 创建表的时候，添加约束 分类 default:默认约束，域完整性 not null：非空约束，域完整性 unique:唯一约束，实体完整性 primary key:主键约束，实体完整性 foreign key：外键约束，参照完整性 check：检查约束(MySQL不...
oracle约束总结（not null/unique/primary key/foreign key/check）
YuZongTao
10-29 4478
约束（constraint）：对创建的表的列属性、字段进行的限制。诸如:not null/unique/primary key/foreign key/check 作用范围：         ①列级约束只能作用在一个列上         ②表级约束可以作用在多个列上（当然表级约束也可以作用在一个列上） 定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义。 — —
mysql常用的约束_MySQL常用的约束条件
weixin_29147045的博客
01-19 303
约束条件约束条件是在表和字段上强制执行的数据检验规则，它是为了防止不规范的数据进入数据库，在我们对数据执行插入、修改、删除等一系列操作的时候，数据库管理系统会自动按照指定的约束条件对数据进行监测，它主要是对空值和重复值的检测，来保证数据存储的完整性和准确性。按照约束的不同功能，MySQL中常用的约束条件有以下六种。1.主键约束主键是表中非空不重复的字段，它可以唯一的标识表中的一条记录。作为主键的字...
SQL约束(主键约束 primary key、外键约束 foreign key、唯一约束unique 、CHECK约束)
Java_c#
08-22 1万+
注意：表中有数据不能创建约束 1、主键约束 primary key 主键是每行的唯一标识符，仅仅通过它就能准确定位到一行，其中主键列在整个表中不能有重复，必须包含唯一的值(不能为NULL)。 2、外键约束 foreign key 外键既能确保数据完整性，也能表现表之间的关系。添加了外键之后，插入引用表的记录要么必须被引用表中被引用列的某条记录匹配，要么外键列的值必须
5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）
BitHachi
04-07 2万+
文章目录0.思维导图1.数据库完整性概述2.实体完整性（1）实体完整性的定义（2）实体完整性检查和违约处理2.参照完整性（1）参照完整性定义（2）参照完整性检查和违约处理 0.思维导图 1.数据库完整性概述 简单介绍： 首先先概述一下数据库完整性指的是什么，数据库完整性指的是数据的正确性和相容性。 数据的正确性是指数据是符合现实世界语义、反映当前实际状况的;比如说人类的性别，只能是男和女。 数...
5.0 数据库完整性详解（PRIMARY KEY、REFERENCES、CHECK、CONSTRAINT、DOMAIN、TRIGGER）
m0_51755061的博客
12-21 1623
文章目录00：引用文章链接==>>BitHachi01：数据库完整性概述（1）DBMS为维护完整性，必须实现如下功能：（2）完整性和安全性区别02：实体完整性—PRIMARY KEY（1）实体完整性的定义（2）实体完整性检查和违约处理03：参照完整性—REFERENCES（1）参照完整性定义（2）参照完整性检查和违约处理04.用户定义的完整性—CHECK（1）属性上的约束条件定义（2）属性上的约束条件检查和处理（3）元组上的约束条件的定义（4）元组上的约束条件检查和违约处理05：完整性约束命名子
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值