MySQL database: 6. Overview and syntax of constraints

kngk 2022-11-24 21:32:12 阅读数:159





​ In order to prevent the existence of data that does not meet the semantic requirements in the database and the input of wrong information、output caused by an invalid operation was raised

​ 为了保证数据的完整性,SQL规范以约束的方式对表数据进行额外的条件限制,从以下四个方面考虑

  • 实体完整性:For example, there cannot be two identical records in a table,无法区分的记录
  • 域完整性:例如年龄范围,性别范围
  • 引用完整性:For example, the class a student is in、学校,Classes should be found in schools、Students can be found in the class
  • 用户自定义完整性:例如用户名唯一,The password must not be empty


​ 约束是指,Mandatory constraints on the data under the field name

​ 约束的方式:

  • 创建表时约束:create table
create table 表名(字段名 类型(数字) 约束条件);


  • 根据约束数据列的限制,约束可分为
    • 单列约束:每个约束只能约束一列
    • 多列约束:每个约束可约束多列数据
  • 根据约束的作用范围,约束可分为
    • 列级约束:只能作用在一个列上,跟在列的定义后面
    • 表级约束:可以作用在多个列上,单独定义
  • 根据约束的作用,约束可分为
    • 非空约束:规定某个字段不能为空
    • 唯一约束:It is stipulated that the data in a certain field cannot be repeated
    • 主键约束:非空且唯一,A table can have only one primary key field
    • 外键约束:It is mainly reflected in the constraint operation of relational data in parent-child tables
    • 检查约束:Integrity constraints that control the values ​​in a particular column
    • 默认值约束:without adding data in particular,The data under this field name uses the default data

4、View existing constraints on the current table


​ show create table 表名;



​ describe 表名;/desc 表名;


二、Constraint syntax and usage


  • 关键词:unsigned

  • 作用:在SQL中,Both integer and floating-point types have their own sign,And many data do not use negative numbers,例如人的年龄,手机号码,At this time, the positive and negative signs can be lifted through the constraints

mysql> create table t2(id tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)



  • 关键词:zerofill

  • 作用:在创建表时,Integer and floating-point types generally do not need to enter numbers,系统会默认生成,After adding data,The system command displays the size and number of digits of the value by default,Pad the additive value with zeros,The actual number of digits of the data value can be fixed,Insufficient parts are filled with zeros

mysql> create table t4(id tinyint(2) zerofill);
Query OK, 0 rows affected (0.01 sec)



  • 关键词:not null

  • 作用:所有字段类型,In the absence of non-null constraints,All inserted data values ​​can be empty(null),During the stage of table creation, the data under this field can be constrained to be non-null,When adding data to the table after constraints,This field cannot be empty,空字符除外

mysql> create table t5(id int(3) zerofill, name varchar(16) not null);
Query OK, 0 rows affected (0.02 sec)




  • 关键词:unique

  • 作用:The unique value means,During the table creation phase,Conditions that can pass constrain unique values,The data added under the constrained fields in the table cannot be repeated,For example, add it under the serial number field‘序号1’后,The serial number will not appear in other columns repeatedly

mysql> create table t6(id int unique, name varchar(16) not null);
Query OK, 0 rows affected (0.04 sec)



  • 关键词:unique(字段名1,字段名2,...)

  • 作用:Union Unique is not defined under a single field name,而是单独定义,Add the unique field name that needs to be combined in the square brackets after the keyword,Then the data values ​​of the added multiple field names cannot appear together,Data values ​​under a single field name can be repeated

mysql> create table t7(id int, name varchar(16), unique(id, name));
Query OK, 0 rows affected (0.02 sec)



  • 关键词:primary key

  • 作用:From the perspective of constraints alone,The primary key is equivalent to ‘非空且唯一’,InnoDBThe storage engine stipulates that all tables must have and can only have one primary key(在创建表的时候,Manual constraints are not required,Other non-null and unique fields will be promoted to primary keys,If there are no constraints,A hidden primary key is automatically generated in the background),Primary keys can speed up searching of data(如果没有主键的话,Finding data needs to be done line by line),When we create a table, we should actively constrain a field as the primary key,用来标识数据的唯一性,通常使用‘id(编号)’字段

mysql> create table t8(id int primary key, name varchar(16));
Query OK, 0 rows affected (0.02 sec)



  • 关键词:auto_increment

  • 作用:Usually when storing multiple data,We will number the data,方便查找,Self-increment means to help us generate the number of data,No need to fill in manually,The advantage of this is that it saves time、省力,There is no need to specifically remember how much the current needs are.

  • 特性:Auto-increment will not be rolled back due to data deletion,only go forward,If you set a larger number yourself,Then the self-increment will be based on this number to a larger number to self-increment,,The auto-increment field can only be the primary key

  • 格式化表格:

    • truncate 表名; (Can be used to format tables and reset auto-increment)
mysql> insert into t9(name, age) values('jason', 18),('kang',19);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0




​ A foreign key is a column in a table,It is included in the primary key of another table

​ Foreign keys are also a type of index,It is a primary key pointing to another table through one table,To associate the two tables

​ A table can have a foreign key,Multiple foreign keys can also exist,Associate with multiple tables


It is mainly used for data association between tables,减少数据冗余,保证数据的一致性,完整性,主要体现在以下两个方面:
  • 阻止执行:
    • 从表插入新行,其外键值不是主表的主键值便阻止插入
    • 从表修改外键值,Modifications are prevented if the new table is not the primary key value of the main table
    • 主表删除行,Its primary key value exists in the secondary table to prevent deletion(To delete the relevant row must be deleted from the table)
    • 主表修改主键值,Old values ​​exist in the secondary table to prevent modification(要想修改,必须先删除从表的相关行)
  • 级联执行:
    • 主表删除行,Jointly deletes the associated row from the table
    • The main table modifies the value,Jointly modify the value of the associated row from the table



create table 主表名(
id 类型 主键 ,
字段名 类型 约束条件)


create table 从表名(
id 类型 主键 ,
字段名 类型(数字) 约束条件,
The name of the field to which the foreign key is bound int,
foreign key(主表名) references 主表名(主表的主键字段名)
  • When creating master and slave tables,主表必须已经存在于数据库中
  • When entering data, you must first enter the data in the main table
  • Foreign key fields cannot be deleted or modified when modifying data

How the cascade is created:

​ Needed at the table creation stage,Join after foreign key key from table:

  • 级联更新:on update cascade
  • 级联删除:on delete cascade
create table 从表名(
id 类型 主键 ,
字段名 类型(数字) 约束条件,
The name of the field to which the foreign key is bound int,
foreign key(主表名) references 主表名(主表的主键字段名)
on update cascade
on delete cascade)

7、4.Multiple relationships with foreign keys


​ 以员工表为例,通常,An employee can only correspond to one department,而一个部门可以对应多个员工,这就是一对多的关系

​ 对于一对多的关系,Foreign key fields should be built in‘多’的一方

create table dep(id int primary key auto_increment,
dep_name varchar(32)
create table emp(id int primary key auto_increment,
emp_name varchar(32) not null,
dep_id int, foreign key(dep_id) references dep(id)
on update cascade
on delete cascade




​ 以书籍表与作者表为例

  • 先站在书籍表的角度
    问:Can a book correspond to multiple authors

  • 再站在作者表的角度
    问:Can one author correspond to multiple books

  • 结论:两个都可以 关系就是'多对多'
    针对'多对多'Cannot be created directly in the table A third relational table needs to be created

create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,2)
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade



​ 以用户表与用户详情表为例

  • 先站在用户表的角度
  • 再站在用户详情表的角度
    问:Can one user detail correspond to multiple users
  • 结论:两个都可以 关系就是'一对一'或者没有关系
    针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userdetail(id)
on update cascade
on delete cascade
create table userdetail(
id int primary key auto_increment,
phone bigint


copyright:author[kngk],Please bring the original link to reprint, thank you.