MySQL 常见约束

Dear 丶 2023-10-06 19:53 37阅读 0赞

#### MySQL 常见约束 ####

*  前言
 *   *  非空约束（not null）
     *  唯一约束（unique）
     *   *  1、设置编号是唯一的（列级约束：该约束只应用于相关的一列上）：
         *  2、给两个列或者多个列添加 unique（表级约束：可以应用在一个表中的多列上）
     *  主键约束( primary key )
     *   *  1、单一主键
         *  2、复合主键
         *  3、自然主键和业务主键
         *  4、MySQL 提供主键值自增（auto\_increment）
     *  外键约束(foreign key）
     *  父表和子表的操作顺序

## 前言 ##

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>非空约束(not null)</td> 
   <td>约束的字段不能为NULL</td> 
  </tr> 
  <tr> 
   <td>唯一约束(unique)</td> 
   <td>约束的字段不能重复</td> 
  </tr> 
  <tr> 
   <td>主键约束(primary key)</td> 
   <td>约束的字段既不能为NULL，也不能重复（简称PK）</td> 
  </tr> 
  <tr> 
   <td>外键约束(foreign key）</td> 
   <td>用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值（简称FK）</td> 
  </tr> 
  <tr> 
   <td>检查约束(check)</td> 
   <td>注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束</td> 
  </tr> 
 </tbody> 
</table>

### 非空约束（not null） ###

not null 修饰的字段，在给表插入数据的时候，不能为空。

create table t_user (
    	no bigint,
        name varchar(255) not null
    );

执行以下语句（name字段为空）：
    insert into t_user(no) values(1);
    
    报错：
    [SQL]insert into t_user(no) values(1);
    [Err] 1364 - Field 'name' doesn't have a default value

也可以给not null字段添加默认值，这样就不会报以上错误：

create table t_user (
    	no bigint,
        name varchar(255) default 'xxx' not null
    );

**注意：`not null` 约束只有列级约束。没有表级约束。**

### 唯一约束（unique） ###

unique 约束的字段不能重复。

#### 1、设置编号是唯一的（列级约束：该约束只应用于相关的一列上）： ####

create table t_user (
    	no bigint unique,
        name varchar(255)
    );

插入相同的编号：
    
    insert into t_user(no,name) values (1,'小明');
    insert into t_user(no,name) values (1,'小龙');
    
    报错：
    Error Code: 1062. Duplicate entry '1' for key 't_user.no'

#### 2、给两个列或者多个列添加 unique（表级约束：可以应用在一个表中的多列上） ####

创建表，设置unique(no,name)

create table t_user(
    	no bigint,
        name varchar(255),
        password varchar(15),
        unique(no,name)
    );

执行以下代码：
    insert into t_user(no,name) values (1,'小明');
    insert into t_user(no,name) values (1,'小龙');
    select * from t_user;
    
    运行是没有错误的，上面建表语句结果unique(no,name)只有在no和name同时相同时才会报错：

**但如下分别设置no和name为unique则并不是表级约束，只是两个列级约束，是会报错的：**

drop table if exists t_user;
    create table t_user(
    	no bigint unique,
        name varchar(255) unique,
        password varchar(15)
    );
    insert into t_user(no,name) values (1,'小明');
    insert into t_user(no,name) values (1,'小龙');

### 主键约束( primary key ) ###

主键约束：  
添加了主键约束，主键字段中的数据不能为NULL，也不能重复。

主键约束、主键字段、主键值：  
表中某个字段添加主键约束后，该字段被称为主键字段，主键字段中出现的每一个数据都被称为主键值；

(1) 添加主键 `primary key` 的字段即不能重复也不能为空，效果不 `“not null nuique”` 相同，但本质是不同的，添加主键约束后，主键不仅会有 `“not null unique”` 作用，而主键字段还会自劢添加“索引 — index”

(2)一张表应该有主键，若没有，表示这张表是无效的（第一范式要求任何一张表都应该有主键），“主键值”是当前行数据的唯一标识，“主键值”是当前行数据的身份证号；（即使表中两行数据完全相同，但是由于主键不同，也认为这是两行完全不同的数据）

**一张表的主键约束只能有1个。**

#### 1、单一主键 ####

单一主键：一张表中的一个字段作为主键。

建表，把编号设置为主键：

create table t_user(
    		no bigint primary key,
            name varchar(255) unique
    );

分别运行下面两段sql，都会报错：
    
    insert into t_user(no,name) values (1,'小明');
    insert into t_user(no,name) values (1,'小龙');
    
    insert into t_user(name) values ('小龙');
    测试得出：no是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复。
    
    使用表级约束方式定义主键：
    create table t_user(
    		no bigint,
            name varchar(255) unique,
            primary key(no)
    );

#### 2、复合主键 ####

drop table if exists t_user;
    create table t_user(
    		no bigint,
            name varchar(255),
            primary key(no,name)
    );

不是复合主键中的所有值都重复，就不算重复
    这样是允许的：
    
    insert into t_user(no,name) values (1,'小明');
    insert into t_user(no,name) values (1,'小龙');
    select * from t_user;

![在这里插入图片描述][4810fd932c2d42c2be511732bbefd60a.png]

#### 3、自然主键和业务主键 ####

自然主键：  
主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）

业务主键：  
主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

**最好不要拿和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。**

#### 4、MySQL 提供主键值自增（auto\_increment） ####

建表，no字段从1开始，以1递增：

drop table if exists t_user;
    create table t_user(
    	no bigint primary key auto_increment,
        name varchar(255)
    );
    insert into t_user(name) values ('a');
    insert into t_user(name) values ('b');
    insert into t_user(name) values ('c');
    insert into t_user(name) values ('d');
    insert into t_user(name) values ('e');
    select * from t_user;

### 外键约束(foreign key） ###

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

外键约束、外键字段、外键值：给某个字段添加外键约束之后，该字段称为外键字段，外键字段中的数据称为外键值。

**定义一个外键时，需要遵守下列规则：**

父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。

外键中列的数目必须和父表的主键中列的数目相同。

外键中列的数据类型必须和父表主键中对应列的数据类型相同。

外键可以为NULL。

建立一个表，area\_number 为1代表是中国地区，为0是海外地区：

drop table if exists t_user;
    create table t_user(
    		no bigint primary key auto_increment,
            name varchar(255),
            area_number int(1),
            area char(4)
    );
    insert into t_user(name,area,area_number) values('小明','中国地区',1);
    insert into t_user(name,area,area_number) values('小龙','中国地区',1);
    insert into t_user(name,area,area_number) values('Ana','海外地区',0);
    insert into t_user(name,area,area_number) values('Jan','海外地区',0);
    insert into t_user(name,area,area_number) values('Fak','海外地区',0);
    insert into t_user(name,area,area_number) values('小法','中国地区',1);
    select * from t_user;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_12_color_FFFFFF_t_70_g_se_x_16]  
可以很清楚地看出此表的缺点：数据太冗余！！

解决方法：我们可以借由两张表来维护这个用户信息。分别用用户表t\_user和地区表t\_area来存储数据。

drop table if exists t_user;
    drop table if exists t_area;
    
    create table t_area(
    		area_number int(1) primary key,
            area char(4)
    );
    create table t_user(
    		no bigint primary key auto_increment,
            name varchar(255),
            a_number int(1),
            foreign key(a_number) references t_area(area_number)
    );
    
    insert into t_area values (1,'中国地区'),(0,'海外地区');
    insert into t_user(name,a_number) values ('小明',1), ('小龙',1),('Ana',0),('Jan',0),('Fak',0),('小法',1);
    
    select * from t_area;
    select * from t_user;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]  
通过两表操作，数据不再冗余。

找出用户的信息：

select u.*,a.* from t_user u join t_area a on u.a_number=a.area_number;

![在这里插入图片描述][0130546c50cf43f7a1cfc48bf7ac3b51.png]

此时t\_area表叫做父表，t\_user叫做子表。

### 父表和子表的操作顺序 ###

删除数据的时候，先删除子表，再删除父表。  
添加数据的时候，先添加父表，再添加子表。  
创建表的时候，先创建父表，再创建子表。  
删除表的时候，先删除子表，再删除父表。

[4810fd932c2d42c2be511732bbefd60a.png]: https://img-blog.csdnimg.cn/4810fd932c2d42c2be511732bbefd60a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_12_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/f7a446e370074377a56c46335c6ed8af.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2djaGVufg==,size_12,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/1a9688f795d347218a29b9e81d508c59.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2djaGVufg==,size_20,color_FFFFFF,t_70,g_se,x_16
[0130546c50cf43f7a1cfc48bf7ac3b51.png]: https://img-blog.csdnimg.cn/0130546c50cf43f7a1cfc48bf7ac3b51.png