专栏首页TechBoxMYSQL回顾(完整性约束相关)

MYSQL回顾(完整性约束相关)

简介

MYSQL完整性约束条件主要包括:

  • auto_increment
  • not null 和 default
  • unique
  • primary key
  • foreign key

unique、 primary key、not null、default相对简单,本篇文章不做记录。

auto_increment

被指定为自增长的字段必须是key 比如primary key 被指定为自增长的字段默认从1开始,默认步长为1 被指定为自增长的字段在插入数据时可以不指定该字段值.(如下 id为主键为自增长)

mysql> desc article;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(10)      | NO   | PRI | NULL    | auto_increment |
| title      | varchar(80)  | YES  |     | NULL    |                |
| createTime | timestamp    | YES  |     | NULL    |                |
| img        | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> insert article(title) value("数据结构");
Query OK, 1 row affected (0.01 sec)
了解即可
%模糊匹配查询任意长度任意字符的变量
mysql> show variables like "auto_inc%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

auto_increment_increment为自增长步长 默认为1 auto_increment_offset 为自增长的起始偏移量 默认为1即第一条记录的id

设置步长

设置会话级别步长

mysql> set session auto_increment_increment=5;

设置全局级别步长

注意:设置全局级别步长需要退出本次会话再次登录才生效
mysql> set global auto_increment_increment=5;

设置起始偏移量

设置会话级别起始偏移量

mysql> set session auto_increment_offset=3;

设置全局级别起始偏移量 注意:设置全局级别起始偏移量需要退出本次会话再次登录才生效

mysql> set global auto_increment_offset=3;

不连续主键

+----+--------------+------------+
| id | title        | createTime |
+----+--------------+------------+
|  1 | NULL         | NULL       |
|  3 | 操作系统      | NULL       |
+----+--------------+——————+

在主键id为1的的记录后面插入了不连贯的一条记录,那么后续自增的记录将是在id为3的基础上自增,即跳过了2;除非手动指定某一条记录的id为2;

mysql> insert into article(title) values("编译原理");
执行上面命令结果如下:

mysql> select * from article;
+----+--------------+------------+
| id | title        | createTime |
+----+--------------+------------+
|  1 | NULL         | NULL       |
|  3 | 操作系统     | NULL       |
|  4 | 编译原理     | NULL       |
+----+--------------+——————+

可以手动插入id为2的记录:

mysql> insert into article(id, title) values(2, "计算机基础”);

mysql> select * from article;
+----+-----------------+------------+------+
| id | title           | createTime | img  |
+----+-----------------+------------+------+
|  1 | NULL            | NULL       | NULL |
|  2 | 计算机基础      | NULL       | NULL |
|  3 | 操作系统        | NULL       | NULL |
|  4 | 编译原理        | NULL       | NULL |
+----+-----------------+------------+------+

foreign key

Foreign key是为了建立表之间的关系,为关联不同的表而生 因为真实的业务中不可能只有一张表。所有的信息都存在于一张表是不是不可以,但是存在于一张表中必然有重复内容,极其浪费空间资源(比如员工和部门,每个员工都有一个对应的部门,一对多关系,有多个员工就要给每个员工分配一个空间存储他的部门信息,相同部门的员工部门信息必然是重复的),也不便于数据统一维护(比如修改某个部门的名字,需要把所有属于该部门的员工记录的部门信息都要修改一遍)

1.创建表

mysql> create table emp(

    -> id int not null primary key,

    -> name char(10),

    -> sex enum('male', 'female'),

    -> dep_id int,

    -> foreign key(dep_id) references dep(id)                                                     

    -> );

ERROR 1824 (HY000): Failed to open the referenced table 'dep'

注意:以上创建emp员工表时关联外键失败,外键指向了dep表的id,因为还不存在dep这张部门表,导致关联失败 所以需要先创建被关联的表(dep)再创建关联表(emp)

先创建被关联表(部门表)

mysql> create table dep(

    -> id int primary key auto_increment,

    -> name char(20)

    -> );

Query OK, 0 rows affected (0.02 sec)

再创建关联表(员工表)

mysql> create table emp(

    -> id int not null primary key auto_increment,

    -> name char(20),

    -> sex enum('male', 'female'),

    -> dep_id int,

    -> foreign key(dep_id) references dep(id)

    -> );

Query OK, 0 rows affected (0.02 sec)

注意:外键在被关联表的中一定是唯一的(unique 或 primary key) 需要将dep表中的id设置为唯一 比如 unique 或 primary key,否则会报错

2.插入数据

插入数据的时候先向被关联表中插入记录 再向关联表中插入记录 如果直接向关联表中插入记录,如果外键在被关联表中不存在会导致插入失败

3.删除数据

先删除关联表中的记录

delete from emp where dep_id=1;

再删除被关联表中的记录

delete from dep where id=1;

反过来执行会报错,因为关联表(员工表)中还存在一些记录的外键指向被关联表(部门表),所以直接删除被关联表(部门表)的记录时不合法的。

4.更新数据

强行更新被关联表中的记录的主键也会报错,因为关联表中还存在一些记录的外键指向被关联表

update dep set id=333 where id=3;

解决方案

创建关联表(员工表)的时候增加删除同步和更新同步

on delete cascade

on update cascade
mysql> create table emp(

    -> id int not null primary key,

    -> name char(10),

    -> sex enum('male', 'female'),

    -> dep_id int,

    -> foreign key(dep_id) references dep(id)

    -> on delete cascade

    -> on update cascade

    -> );

Query OK, 0 rows affected (0.02 sec)

注意:下图红框中换行无逗号 因为同属于一句

image.png

增加了删除同步和更新同步,建表方面:建表时还是需要先建立被关联表再建关联表。插入记录方面:还是要先向被关联表中插入记录,再向关联表中插入记录。

删除同步更新同步并不解决建表和插入记录操作的先后问题 删除同步是指删除被关联表的记录同步更新关联表 更新同步是指更新被关联表的记录同步更新关联表

增加了删除同步和更新同步后,更新部门表中的记录和删除部门表的记录不再会报错,会同步更新或删除员工表中的记录

尽量不要使用foreign key

建议:尽量不要使用foreign key,foreign key会限制表之间的关系,表之间的关联关系尽量在程序层面去维护,使用代码去维护表之间的这种关联关系(更新同步、删除同步)

设置外键时可以通过constraint给外键起一个名字(可选)

image.png

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【iOS】关联对象详解前言参考文章

    VV木公子
  • 利用Clang探究block的本质

    block作为Objective-C语言中的一种特殊的存在,已经为大家所熟知。在其他语言中,也有类似于block的实现,比如JavaScript和Swift中的...

    VV木公子
  • MYSQL回顾(表关系相关)

    比如有两个表,分别是书籍表和出版社表。书籍和出版社是典型的多对一关系,即一本书只能由一个出版社出版,一个出版社可以出版多本书。则书籍表应该有一个外键press_...

    VV木公子
  • 修改Nuget包默认存放路径

    默认情况下所有的Nuget包都会下载到C盘,目前我这边有几十个G的大小,这导致我C盘的容量越来越小…

    HueiFeng
  • MySQL EXPLAIN SQL 输出信息描述

    Leshami
  • 进一步推动国产替代 联想全新云办公解决方案面世

    “三个月内实现2000人的桌面办公需求,未来要支撑总计不小于2万个虚拟桌面的部署。” 某省级行政中心在搬迁重建时向联想企业网盘表达了这样的信息建设诉求。而在实现...

    联想Filez
  • TensorFlow读取数据

    本文介绍如何使用TensorFlow来读取图片数据,主要介绍写入TFRecord文件再读取和直接使用队列来读取两种方式。假设我们图片目录结构如下:

    superhua
  • pytorch版本的OpenNMT多任务编译实践

    OpenNMT 是哈佛大学 NLP 研究小组开源的产品级翻译工具,在其官网上有多种语言代码,有基于 Torch/Pytorch,也有基于tens...

    sparkexpert
  • ES6系列_16之模块化操作

    export可以让我们把变量,函数,对象进行模块化,提供外部调用接口,让外部进行引用。先来看个最简单的例子,把一个变量模块化。我们新建一个demo.js文件,然...

    wfaceboss
  • 正则十八式-第四式 挫骨扬灰

    张风捷特烈

扫码关注云+社区

领取腾讯云代金券