一文理解MySQL数据库的约束与表的设计

目录
  • 1.数据库约束
    • 1.1 约束类型
    • 1.2 null 约束
    • 1.3 unique 唯一的约束
    • 1.4 default 默认值约束
    • 1.5 primary key 主键约束
    • 1.6 primary key auto_increment 自增主键
    • 1.7 foreign key 外键约束
  • 2.表的设计
    • 2.1 一对一
    • 2.2 一对多
    • 2.3 多对多
  • 总结

1.数据库约束

约束是关系型数据库提供的一种校验数据合法性的机制.

1.1 约束类型

约束类型 说明 示例
null 使用not null 指定列不为空 name varchar(20) not null;
unique 指定列为唯一的,不重复的 name varchar(20) unique;
default 指定列为空时的默认值 name varchar(20) default "无名氏";
主键约束 not null 和unique 的结合 id int primary key;
外键约束 关联其他表的主键或唯一键 foreign key (字段名) references 主表(列)
check约束 保证列的值符合指定的条件 check(sex='男'or sex='女')

1.2 null 约束

创建表时指定某列不为空.

语法:

create table 表名(字段1 类型 not null,字段2 类型...);

示例:

1.3 unique 唯一的约束

创建表时指定某列为唯一的不重复的.

语法:

create table 表名(字段1 类型 unique,字段2 类型....);

示例:

1.4 default 默认值约束

指定列为空时的默认值

语法:

create table 表名(字段1 类型 default 默认值,字段2 类型....);

示例:

1.5 primary key 主键约束

创建表时指定列有唯一确定的标识符

语法:

create table 表名(字段1 类型 parmary key,字段2 类型...);

示例:

学生id 不为null 且不能重复.

1.6 primary key auto_increment 自增主键

对于整数类型的主键, 常搭配自增auto_increment变为自增主键, 插入字段不给值时, 使用最  大值+1.

语法:

create table 表名(字段1 int primary key auto_increment,字段2 类型...);

示例:

分布式系统中唯一id生成算法 

对于较小的数据量, 所有的数据都在一台MySQL服务器上存储,此时自增主键可以很好的工作, 但如果涉及的数据量较为庞大, 需要将数据部署到多台MySQL服务器上, 这时再插入数据就无法保证自增主键的唯一性.因此MySQL内部采用分布式系统中唯一id生成算法.

实现公式:

时间戳+主机编号+随机因子 结合这三部分就可以得到一个全局唯一的id.

1.7 foreign key 外键约束

MySQL外键是用来在两个表之间建立连接,可以是一列或多列,一个表可以有多个外键.

  • 外键是本表的一个字段, 不是本表的主键, 但对于另一个表的主键.
  • 定义外键之后不允许删除另一个表中具有关联关系的行.
  • 主表(父表): 相关联字段中主键所在的表就是主表.
  • 从表(子表): 相关联字段中外键所在的表就是从表.

语法:

添加外键约束

(constraint 外键名) foreign key (字段名1),(字段名2..) reference 主表名(主键列1,主键列2..)

修改表时添加外键约束 

alter table 表名 add (constraint 外键名) foreign key (列名) references 主表名(列名);

删除外键约束

alter table 表名 drop forigen key (外键名);

示例:

MySQL表中, 没有默认对两张有关联的表提供检查机制, 例如:下图中插入非法班级, 不会报错. 因此需要引入外键约束.

加入外键约束之后再插入非法数据就会报错.

非法修改已插入的数据也会报错.

Tips:

  • 父表必须已存在数据库中或是当前正在创建的表.
  • 父表中的主键不能出现空值, 但外键可以.所以通常用primary key来约束主键.
  • 外键中列是数目和数据类型必须和父表的主键一一对应.
  • 父表限制子表的插入,字表也约束父表的删除.(互相制约)

父表中元素的删除:

实际应用中删除父表中数据的方式通常是逻辑删除. 当一个商品下架之后, 为了让客户可以查看到历史订单, 所以不能直接删除商品信息. 通常需要在建表之初预留商品下架的字段, 商品下架之后, 给商品下架的字段标记赋值达到伪删除的效果.

2.表的设计

基本思路:

  • 先明确实体
  • 再明确实体之间的联系
  • 根据上述内容, 嵌套到下面的公式中, 就可做出表.

示例:

粗略设计一个学生教务管理系统.

  • 确定实体为: 学生, 课程, 学生账号, 学生班级
  • 学生与课程之间存在关系, 学生与账号之间存在关系, 学生与班级之间存在关系.
  • 学生与课程:多对多, 学生与账号:一对一, 学生与班级:一对多.

2.1 一对一

一个人只能有一个身份证号码, 一个身份证号码只对应一个人.

2.2 一对多

一个班级有多个学生, 一个学生只对应一个班级.

2.3 多对多

一个学生可以选择多个课程, 一个课程也可以被多个学生选择.

总结

到此这篇关于一文理解MySQL数据库的约束与表设计的文章就介绍到这了,更多相关MySQL约束与表设计内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL 常见的数据表设计误区汇总

    误区一:过多的数据列 MySQL 存储引擎的 API 是按照行缓冲区方式从服务端和存储引擎复制数据.服务端将缓冲区数据解码成数据列.然而,将行缓冲区的格式转换为数据行数据结构的列可能会代价很高.MyISAM 固定使用与服务端匹配的行格式,因此无需转换.然而,MyISAM 的可变行格式以及 InnoDB 的行格式总是需要进行转换.转换的代价依赖于列的数量.如果当数据表的列超过上百列的时候,会引起很高的 CPU 资源消耗--即便是使用到的列很少.曾经看过一篇文章,指的是一个多语言的解决方案,直接简单

  • MySQL约束超详解

    目录 MySQL约束操作 1.非空约束 2.唯一约束 3.主键约束 4.外键约束 5.级联 MySQL约束操作 概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性. 分类: 主键:primary key 非空约束:not null 唯一约束:unique 外键约束:foreign key 1.非空约束 not null,值不能为空. 创建表时添加非空约束: CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL ); 创建表完后,添加非空

  • 浅谈mysql 树形结构表设计与优化

    浅谈mysql 树形结构表设计与优化

    前言 在诸多的管理类,办公类等系统中,树形结构展示随处可见,以"部门"或"机构"来说,接触过的同学应该都知道,最终展示到页面的效果就是层级结构的那种,下图随机列举了一个部门的树型结构展示图 设计考虑因素 1.表结构设计 稍稍有点开发和表结构设计经验的同学,设计出这样一张表,应该很容易,只需要在depart表中,添加一个pid/字段即可满足要求,参考下表: CREATE TABLE `depart` ( `depart_id` varchar(32) NOT NULL

  • mysql增加外键约束具体方法

    本教程操作环境:windows7系统.mysql8版本.Dell G3电脑. MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用.对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表). 外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性. 定义外键时,需要遵守下列规则: 主表必须已经存在于数据库中,或者是当前正在创建的表.如果是后一种情况,则主表与从表是同一个表,这样的表

  • MySQL约束类型及举例介绍

    约束 约束保证数据的完整性和一致性 约束分为表级约束和列级约束 约束类型包括:NOT NULL (非空约束),PRIMARY KEY(主键约束),UNIQUE KEY(唯一约束),DEFAULT(默认约束),FOREIGN(外键约束) 1.主键约束 PRIMARY KEY 每张数据表只能存在一个主键 主键保证记录的唯一性,主键的值不重复 主键自动为 NOT NULL 举例,创建一张student表,将学号设置为主键创建完表之后,通过SHWO COLUMNS FROM student查看表结构 C

  • 详解MySQL中的外键约束问题

    使用MySQL开发过数据库驱动的小型web应用程序的人都知道,对关系数据库的表进行创建.检索.更新和删除等操作都是些比较简单的过程.理论上,只要掌握了最常见的SQL语句的用法,并熟悉您选择使用的服务器端脚本语言,就足以应付对MySQL表所需的各种操作了,尤其是当您使用了快速MyISAM数据库引擎的时候.但是,即使在最简单的情况下,事情也要比我们想象的要复杂得多.下面我们用一个典型的例子进行说明.假设您正在运行一个博客网站,您几乎天天更新,并且该站点允许访问者评论您的帖子. MySQL外键约束条件

  • MySQL外键约束常见操作方法示例【查看、添加、修改、删除】

    本文实例讲述了MySQL外键约束常见操作方法.分享给大家供大家参考,具体如下: 1. 查看数据库表创建的sql语句 show create table vip 2. 查看外键的约束名 CREATE TABLE `vip` ( `id` int(11) NOT NULL AUTO_INCREMENT, `address` varchar(255) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `mobile` varchar(255) DEFAUL

  • MySQL外键约束的禁用与启用命令

    MySQL外键约束的禁用与启用: MySQL外键约束是否启用是通过一个全局变量标示的: FOREIGN_KEY_CHECKS=0; 禁用状态 FOREIGN_KEY_CHECKS=1; 启用状态 查看当前FOREIGN_KEY_CHECKS的值可用如下命令: SELECT @@FOREIGN_KEY_CHECKS; 禁用外键约束: SET FOREIGN_KEY_CHECKS=0; 启用外键约束: SET FOREIGN_KEY_CHECKS=1; 以上这篇MySQL外键约束的禁用与启用命令就是

  • Mysql树形结构的数据库表设计方案

    Mysql树形结构的数据库表设计方案

    目录 前言 一.基本数据 二.继承关系驱动的设计 三.基于左右值编码的设计 四.树形结构CRUD算法 (1)获取某节点的子孙节点 (2)获取某节点的族谱路径 (3)为某节点添加子孙节点 (4)删除某节点 五.总结 参考文献 前言 最近研究树形菜单网上找了很多例子看了.一下是网上找的一些资料,然后自己重新实践,记录下免得下次又忘记了. 程序设计过程中,我们常常用树形结构来表征某些数据的关联关系,如企业上下级部门.栏目结构.商品分类等等,通常而言,这些树状结构需要借助于数据库完成持久化.然而目前的各

  • 一文理解MySQL数据库的约束与表的设计

    一文理解MySQL数据库的约束与表的设计

    目录 1.数据库约束 1.1 约束类型 1.2 null 约束 1.3 unique 唯一的约束 1.4 default 默认值约束 1.5 primary key 主键约束 1.6 primary key auto_increment 自增主键 1.7 foreign key 外键约束 2.表的设计 2.1 一对一 2.2 一对多 2.3 多对多 总结 1.数据库约束 约束是关系型数据库提供的一种校验数据合法性的机制. 1.1 约束类型 约束类型 说明 示例 null 使用not null 指

  • MySQL 数据库的约束及数据表的设计原理

    MySQL 数据库的约束及数据表的设计原理

    目录 1. 数据库的约束 1.1 介绍 1.2 约束类型 1.3 not null 1.4 unique 1.5 default 1.6 primary key 1.7 foreign key 1.8 check 2. 数据库表的设计 2.1 一对一关系 2.2 一对多关系 2.3 多对多关系 1. 数据库的约束 1.1 介绍 数据库中的约束 ,顾名思义即是对插入数据库中的数据进行限定,这么做的目的是为了保证数据的有效性和完整性. 这样就大幅度地提高了数据库中数据的质量,节省了数据库的空间和调用

  • MySQL 数据库的约束及数据表的设计原理

    MySQL 数据库的约束及数据表的设计原理

    目录 1.数据库的约束 1.1介绍 1.2约束类型 1.3notnull 1.4unique 1.5default 1.6primarykey 1.7foreignkey 1.8check 2.数据库表的设计 2.1一对一关系 2.2一对多关系 2.3多对多关系 1. 数据库的约束 1.1 介绍 数据库中的约束 ,顾名思义即是对插入数据库中的数据进行限定,这么做的目的是为了保证数据的有效性和完整性. 这样就大幅度地提高了数据库中数据的质量,节省了数据库的空间和调用数据的时间. 之前介绍过 MyS

  • MySQL示例讲解数据库约束以及表的设计

    目录 数据库约束 not null unique default primary key 主键 foreign key 外键 check 表的设计 一对一 一对多 多对多 数据库约束 not null 指定某列的存储不能为null值 create table student (id int not null,name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> desc student; +-------+---------

  • MySQL数据库的约束限制详解

    目录 一.介绍 二.操作 添加 删除 外键联级操作 一.介绍 数据库的约束是对表中数据进行的一种限制,为了保证数据的正确性.有效性.完整性. 无论是在添加数据还是在删除数据的时候,都能提供帮助.所有的关系型数据库都支持对数据表的约束. 主键:唯一标识一条记录,不能重复,不允许为空.主要用来保证数据的完整性. 外键: 表的外键是另一表的主键,外键可以有重复,可以为控制.主要用来和其他表建立联系. 二.操作 添加 添加主键: // 一般设置id为主键 CREATE TABLE student( id

  • MySQL数据库优化之分表分库操作实例详解

    本文实例讲述了MySQL数据库优化之分表分库操作.分享给大家供大家参考,具体如下: 分表分库 垂直拆分 垂直拆分就是要把表按模块划分到不同数据库表中(当然原则还是不破坏第三范式),这种拆分在大型网站的演变过程中是很常见的.当一个网站还在很小的时候,只有小量的人来开发和维护,各模块和表都在一起,当网站不断丰富和壮大的时候,也会变成多个子系统来支撑,这时就有按模块和功能把表划分出来的需求.其实,相对于垂直切分更进一步的是服务化改造,说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务,通过服务间的

  • 解决MySQL数据库意外崩溃导致表数据文件损坏无法启动的问题

    问题故障: MySQL数据库意外崩溃,一直无法启动数据库. 报错日志: 启动报错:service mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL. ERROR! The server quit without updating PID file (/www/wdlinux/mysql/var/iZ2358oz5deZ.pid). 数据库error日志: 200719 22:07:43 I

  • MySQL数据库查询之多表查询总结

    MySQL数据库查询之多表查询总结

    目录 多表关系 表与表之间的联系: 一对多(多对一) 多对多 一对一 多表查询 多表查询的分类 1.连接查询: 2.子查询 内连接 外连接 自连接 联合查询 子查询 标量子查询 列子查询 行子查询 表子查询 多表查询案例 总结 多表关系 在进行数据库表结构的设计时,会根据业务的需求和业务模块之间的关系,分析设计表结构,由于业务之间相互关联,所以各个表结构之间也存在各种联系 表与表之间的联系: 1.一对多(多对一) 2.多对多 3.一对一 一对多(多对一) 例如,一个员工对应一个部门,一个部门可以

  • Mysql数据库分库和分表方式(常用)

    Mysql数据库分库和分表方式(常用)

    本文主要给大家介绍Mysql数据库分库和分表方式(常用),涉及到mysql数据库相关知识,对mysql数据库分库分表相关知识感兴趣的朋友一起学习吧 1 分库 1.1 按照功能分库 按照功能进行分库.常见的分成6大库: 1 用户类库:用于保存了用户的相关信息.例如:db_user,db_system,db_company等. 2 业务类库:用于保存主要业务的信息.比如主要业务是笑话,用这个库保存笑话业务.例如:db_joke,db_temp_joke等. 3 内存类库:主要用Mysql的内存引擎.

  • MySQL 外键约束和表关系相关总结

    目录 外键(Foreign Key) 如何确定表关系 如何建立表关系 一对多关系 - 员工表和部门表 多对多 一对一 表关系总结 外键(Foreign Key) 按照上述所说,一张表存储员工信息会极大的浪费资源,重复数据太多,这个问题就类似于将所有的代码都写在了一个py文件中,因此我们可以将一个表拆成不同的表,在这不同的表之间建立关联,而建立关联就需要使用外键foreign key.外键也属于约束条件的一种. 如何确定表关系 表与表之间的关系有三种一对多.多对多.一对一.那么如何确定表与表之间的

随机推荐