MySQL创建表时加入的约束以及外键约束的的意义

Wesley13
• 阅读 537

1,创建表时加入的约束

a) 非空约束,not null

b) 唯一约束,unique

c) 主键约束,primary key

d) 外键约束,foreign key

1,非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空

drop table if exists t_student; 
create table t_student(
    student_id      int(10),
    student_name     varchar(20) not null,
    sex        char(2)  default  'm',
    birthday    date, 
    email        varchar(30),
    classes_id    int(3)    
)

反例,如果插入时 student_name为空违反了约束则报错

insert into t_student(student_id, birthday, email, classes_id) 
values
(1002, '1988-01-01', 'qqq@163.com', 10)

2,唯一性约束,它可以使某个字段的值不能重复,如:email不能重复

drop table if exists t_student; 
create table t_student(
    student_id      int(10),
    student_name     varchar(20) not null,
    sex        char(2)  default  'm',
    birthday    date, 
    email        varchar(30)  unique,
    classes_id    int(3)    
)

drop table if exists t_student; 
create table t_student(
    student_id      int(10),
    student_name     varchar(20) not null,
    sex        char(2)  default  'm',
    birthday    date, 
    email        varchar(30)  ,
    classes_id    int(3)    ,
constraint email_unique unique(email)/*表级约束*/

3,主键约束

  每个表应该具有主键,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的。

drop table if exists t_student; 
create table t_student()
    student_id      int(10)  primary key,/*列级约束*/
    student_name     varchar(20) not null,
    sex        char(2)  default  'm',
    birthday    date, 
    email        varchar(30)  ,
    classes_id    int(3)    
)

或者

drop table if exists t_student; 
create table t_student(
    student_id      int(10),
    student_name     varchar(20) not null,
    sex        char(2)  default  'm',
    birthday    date, 
    email        varchar(30)  ,
    classes_id    int(3),
    CONSTRAINT p_id PRIMARY key (student_id)/*表级约束*/
)

4,外键约束

外键主要是维护表之间的关系的,主要是为了保证参照完整性,如果表中的某个字段为外键字段,那么该字段的值必须来源于参照的表的主键。

首先建立班级表t_classes

drop table if exists t_classes;
create table t_classes(
    classes_id int(3),
    classes_name varchar(40),
    constraint pk_classes_id primary key(classes_id)
)

在t_student中加入外键约束

drop table if exists t_student;
create table t_student(
    student_id       int(10),
    student_name     varchar(20),
    sex           char(2),
    birthday      date,
    email         varchar(30),
    classes_id    int(3),
    constraint    student_id_pk primary key(student_id),
   constraint    fk_classes_id foreign key(classes_id) references t_classes(classes_id)       
)

当我们向t_student中加入数据

insert into t_student(student_id, student_name, sex, birthday, email, classes_id)values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)

出现错误,因为在班级表中不存在班级编号为10班级,外键约束起到了作用

存在外键的表就是子表,参照的表就是父表,所以存在一个父子关系,也就是主从关系,主表就是班级表从表就是学生表。

上面的例子中如果插入的时候把外键值设为null,可以插入成功。成功的插入了学生信息,但是classes_id没有值,这样会影响参照完整性,所以我们建议将外键字段设置为非空。

当需要删除班级数据,也会报错,因为子表(t_student)存在一个外键classes_id,它参照了父表(t_classes)中的主键,所以先删除子表中的引用记录,再修改父表中的数据。

因为子表(t_student)存在一个外键classes_id,它参照了父表(t_classes)中的主键,所以先删除父表,那么将会影响子表的参照完整性,所以正确的做法是,先删除子表中的数据,再删除父表中的数据,采用drop table也不行,必须先drop子表,再drop父表

例如级联更新

mysql对有些约束的修改比较麻烦,所以我们可以先删除,再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id)  on update cascade;

MySQL创建表时加入的约束以及外键约束的的意义

级联之后,我们只修改了父表中的数据,但是子表中的数据也会跟着变动。

2,需不需要外键

主键和索引是不可少的,不仅可以优化数据检索速度,开发人员还省不其它的工作,

矛盾焦点:数据库设计是否需要外键。这里有两个问题:一个是如何保证数据库数据的完整性和一致性;二是第一条对性能的影响。

正方观点:
1,由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。
eg:数据库和应用是一对多的关系,A应用会维护他那部分数据的完整性,系统一变大时,增加了B应用,A和B两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性,而且一年以后如果又增加了C应用呢?
2,有主外键的数据库设计可以增加ER图的可读性,这点在数据库设计时非常重要。
3,外键在一定程度上说明的业务逻辑,会使设计周到具体全面。

反方观点:
1,可以用触发器或应用程序保证数据的完整性
2,过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
3,不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在insert,   update,   delete   数据的时候更快)
eg:在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不 止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!

结论:
1,在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
2,用外键要适当,不能过分追求
3,不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。

需要注意的是:MySQL允许使用外键,但是为了完整性检验的目的,在除了InnoDB表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异,实际上却非常正常:对于数据库的所有外键的每次插入、更新和删除后,进行完整性检查是一个耗费时间和资源的过程,它可能影响性能,特别是当处理复杂的或者是缠绕的连接树时。因而,用户可以在表的基础上,选择适合于特定需求的最好结合。所以,如果需要更好的性能,并且不需要完整性检查,可以选择使用MyISAM表类型,如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能,最好选择表结构为innoDB类型

转自;https://www.cnblogs.com/fuland/p/4280434.html

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Easter79 Easter79
2年前
sqlserver2005创建唯一约束的方法
对于一个表中非主键列的指定列,唯一(UNIQUE约束|:强制非主键上的实体完整性的约束。UNIQUE约束确保未输入重复值,并创建一个索引以增强性能。)约束确保不会输入重复的值。例如,在employee表中emp\_id列是主键,可以定义一个唯一约束来要求表中社会安全号码(ssn)列的项是唯一的。在数据库关系图中,可以使用"索引/键"属性页创建、
Wesley13 Wesley13
2年前
MySQL字段约束及多表查询
前言:mysql的字段约束是以后必不可免的,下面主要写了四个:主键约束用于唯一且不能为空;非空约束即不能为空可以重复;唯一约束即可以为空但必须唯一;外键约束是让表与表之间有一定的关联;当然如何使用还看下文,多表就不在这总结了。如果你对前面的知识有所遗忘或感兴趣MySQL数据库表的模糊/多行/分组/排序/分页查询以及字mysql数据类型的讲解
Stella981 Stella981
2年前
Go 语言编程 — gorm 的数据完整性约束
目录文章目录目录实体完整性(主键约束)用户定义完整性(非空约束、唯一约束、检查约束和默认值)参照完整性(外键约束)关联关系一对一、一对多关联多对多关联示例实体完整性(主键约束)每个关系(表)至少存在一个主
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL中添加、删除约束
MySQL中6种常见的约束:主键约束(primarykey)、外键约束(foreignkey)、非空约束(notnull)、唯一性约束(unique)、默认值约束(defualt)、自增约束(aoto\_increment),下面是添加、删除这几种约束的一些方法。\我已经建了数据库;1\添加约束21、建表时添加约
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这