约束
限制
是防止数据库中,存在不符合语义规定的数据,防止因错误信息的输入输出
造成无效操作或错误信息而提出
约束的方式对表数据进行额外的条件限制
约束的分类
根据约数据列的限制:
单列约束:每个约束只约束一列
多列约束:约束多列
根据约束的作用范围:
列级约束:只能作用在一个列上,跟在列的定义后面
表级约束:可以作用在多个列上,不与列一起,而是单独定义
根据约束起的作用:
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)约束
FOREIGN KEY 外键约束
CHECK 检查约束
DEFAULT 默认值约束
在CREATE TALBE 或ALTER TABLE 时候添加约束
在ALTER TABLE 还可以删除约束
查看约束
第一种:
表的索引里面
第二种:
在系统自带的information_schema.table_constraints表中查看某个表的约束
Schema纲要 constraint 约束、限制
非空约束
NOT NULL
只能单独限定列,只有列级约束
添加非空约束:
CREATE 或ALTER CHANGE 或 ALTER MODIFY
删除非空约束:
在ALTER中,将NOT NULL改为NULL
唯一性约束
UNIQUE
用来限制某个字段的值不能重复
可以是某一个列的值唯一(列级约束),可以多个列组合的值唯一(表级约束)
不给唯一约束命名,则与字段名相同。复合约束若不起名,与第一个字段名一样
MySQL会给唯一约束的列上默认创建一个唯一索引
可以添加多个NULL值
添加唯一约束:
CREATE 或ALTER
在字段后,表级约束写在最后,通过CONSTRAINT(约束)+UNQIUE()
其中book2_book_name是约束名
通过ALTER进行唯一约束:add/modify/change
添加复合唯一约束:
UNIQUE(字段1,字段2...) 多个字段的组合是唯一的,
多个字段中只要有一个字段不一样,就可以添加
删除唯一约束:
通过唯一索引(约束名)方式删除 INDEX索引
括号()前是索引名,括号里是约束的字段,最后是约束类型
主键约束
PRIMARY KEY
唯一标识表中的一行记录
相当于唯一约束+非空约束的组合,且会根据主键大小自动排序
列级约束和表级约束,任意字段都不允许为空,且复合不允许重复
一个表一定有一个主键约束,若不指定则MySQL自动会指定
主键的索引名固定是PRIMARY
不要修改主键字段的值,主键是数据记录的唯一标识,修改会破坏数据完整性
添加主键约束:create/alter
添加表级约束主键
建表后添加主键约束
删除主键约束:在实际中不会删除主键约束,会破环数据完整性
对id设置主键,若先添加id=100,再添加id=99,则id=99会排在结果集的前面
自增列
AUTO_INCREMENT
一个表只能有一个自增列
作用字段必须是整数类型
字段必须是主键列或唯一键列
若在自增列赋值0或NULL,仍会自增
创建自增列:CREATE
若给id赋值1000,则接下来自增到1001
建表后自增列:ALTER
删除自增约束:
MySQL8.0自增新特性
自增变量持久化
对自增列添加三次数据
删除最后一行记录
再次添加一次数据,此时从4开始自增
这一现象在5.7和8.0都会出现
区别在于:
5.7版本自增由计数器决定,在内存中,在数据库重启后,计数器会初始化。
把id=4记录删掉,重启后,再次自增则从3开始
8.0版本自增在重做日志中,重启后仍从日志中读取自增数值
把id=4记录删掉,重启后,再次自增则从5开始
所以叫做自增持久化
外键约束
FOREIGN KEY
限定某个表的某个字段的引用完整性
A表中的字段和B表相关联
只有B表该字段存在的值,A表才能赋该值
同时也是互相约束,B表不能删除已经在A表中存在的记录
主表(父表):被引用的表,被参考的表
从表(子表):引用别人的表,参考别人的表
特点:
B表的字段必须是主键或唯一键
该约束的默认名不是列名,而是自动产生一个外键名,所以自己命名更好
创建(CREATE)表时就指定外键约束的话,先创建主表,再创建从表
删表时,先删从表(或先删除外键约束),再删除主表
在“从表”中指定外键约束,并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样
创建外键:
[CONSTRAINT] FOREIGN KEY(从表的某个字段) references 主表名(被参考字段)
先创建主表,外键字段必须是主键或唯一键
再创建从表
添加数据,2表外键字段赋值必须在1表中存在
建表后创建外键:ALTER
用的情况较少,一般在创建表时完成外键
外键约束等级:
Cascade方式 :在父表上update/delete记录时,同步update/delete掉子表的匹配记录
Set null方式 :在父表上update/delete记录时,将子表上匹配记录的列设为null,但是要注意子 表的外键列不能为
not null No action方式 :如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式 :同no action, 都是立即检查外键约束
Set default方式 (在可视化工具SQLyog中可能显示空白):父表有变更时,子表将外键列设置 成一个默认的值,但Innodb不能识别
不设置,默认是No action方式
推荐设置ON UPDATE CASCADE ON DELETE RESTRICT
即主表UPDATE操作更新从表,主表DELETE操作从表修改为NULL
删除外键约束:
第一步,查看表的外键约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名';
删除外键约束名
第二步,查看索引名和删除索引(索引要手动删除)
查看索引
删除索引
外键约束实际开发中
不建议使用
更应该在应用层进行约束,即在Java程序中进行限定
外键约束消耗系统资源,导致系统开销变慢
外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度
CHECK约束
检查某个字段的值是否符合自己规定的要求
一般是值的范围
MySQL8.0新的支持约束
创建CHECK约束:
限制year大于18,且限制性别的值
报错violated违反
在创建表后CHECK约束:“
删除约束
DEFAULT约束
给字段设置默认值
插入数据若没有赋值,则为默认值
创建DEFAULT约束:
插入值
创建表后约束:
再添加一个字段,并约束DEFAULT
默认空字段
DEFAULT 设置为0或者 ‘ ‘空字段能够防止为NULL,影响运算
删除DEFAULT