MySQL 表约束

行者花雕 2022-03-17

展开全文

约束概述

对数据表中数据的限制条件叫表的约束，目的是为了保证表中记录的完整和有效。例如非空、唯一等。

查看约束

1 通过查看建表语句查看表中的约束

show create table tb_name;

2 通过检查约束表查看约束

select * from information_schema.table_constraints where table_name='usr';

Tips：在MySQL数据库中所有的约束都存放在information_schema.table_constraints表中～～～～参看～～～～～information_schema数据库介绍

主键约束

什么是主键约束

在MySQL中主键是一种约束，也是一种索引。被设为主键的字段要求不重复不能为空不能缺省。主键的作用为唯一的确定表中的一条记录。需要注意的是当主键是复合主键时，不重复的含义是组合起来不重复即可，单列值允许重复，但不允许同时重复

创建主键

1 在字段中定义主键

create table if not exists user(
    id int(11) auto_increment primary key,
  	name char(64) not null default 'No Name'
);

2 字段定义结束后定义主键

create table if not exists user(
		id int(11) auto_increment,
  	name char(64) not null default 'No Name',
  	primary key(id,name)
);
-- 注意 声明主键的完整格式应该是这样的
-- constraint 约束名 primary key key_name

3 建表完成后添加主键

上边的两种方式都是在创建表时候指定主键约束，也可以在表建成后添加主键约束

alter table tb_name add primary key(key_name)
-- tb_name   表名
-- primary key() 括号里填写字段名,可以是一个 也可以是多个(复合主键)

删除主键

-- 如果主键字段中包含有自增 auto_increment，需要先删除自增属性 在删除主键
-- 如果没有自增直接删除主键即可

-- 删除自增类型。 ！！！注意 自增是类型 所以 可以使用 修改字段的类型就ok

--1 修改字段的名 修改后需要重新指定类型
alter table tb_name change col_name col_name int;

--2 简单的直接修改类型
alter table tb_name modify col_name int;

-- 删除掉 auto_increment属性后可以删除主键
alter table tb_name drop primary key;

主键的分类

1 单一主键

创建 1 中创建的 id 就是单一的主键，需要注意，定义字段时声明主键是无法创建复合主键的。

2 复合主键

创建 2 中的创建主键的方式允许创建单一主键也允许创建复合主键，推荐使用这种方法进行主键的创建。

3 联合主键

当处理多对多的关系时，需要创建中间表来进行多对多的关联，此时中间表的主键成为联合主键，他能唯一的确定多对多的若干实体间的对应关系。联合主键也可以是复合主键即联合主键由第三表的多个列复合而成。保持唯一。

外键约束

什么是外键

外键依托于具有唯一约束的字段，一般来讲主键会多一些，但是也可以不是主键。前提是它们具有唯一约束。外键依赖的字段可以来自本（自参照表）也可以是其他表，一般的是其他表的情况多一些。此时要求 主表必须已经存在于数据库中，或者是当前正在创建的表。如果被依赖字段在本表中，那么这样的约束结构称为自参照完整性。

Tips： 虽然主键不允许为空不允许重复，但是外键可以为空，也可以重复。外键约束要求每一个值都来自于主键，特别的插入空值时，MySQL不支持缺省，必须显式表达出来，例如

insert into user(name,leader_id) values('monkey',null);  
-- 显示的写出 null 否则 会报错
insert into user(name,leader_id) values('monkey');  
-- 是不合法的语句

在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
外键中列的数目必须和父表的主键中列的数目相同。
外键中列的数据类型必须和父表主键中对应列的数据类型相同。

创建外键

1 建表时创建外键

[constraint <fk_name>] foreign key(fk_col_name [，col_name2，…])
references <tb_name> (pk_col_name1 [，pk_col_name2，…])
-- fk_name				外键名
-- fk_col_name		设置外键字段的字段名
-- tb_name				外键关联的主表表名
-- pk_col_name 		主表被依赖的字段名

2 建表完成后创建

alter table tb_name add [constriant <fk_name>] foreign key(fk_name) references tb_name(pk_col_name)
-- fk_name				外键名
-- fk_col_name		设置外键字段的字段名
-- tb_name				外键关联的主表表名
-- pk_col_name 		主表被依赖的字段名

例子

-- 创建主表 leader
create table leader(
  	id int(11) primary key auto_increment, 
  	name char(64) not null
);

-- 创建外键所在表 employee
create table employee(
  	id int(11) primary key auto_increment, 
  	name char(64) not null,
  	leader_id int(11),
  	constraint fk_leader_employee foreign key(leader_id) references leader(id)
);
create table employee(
  	id int(11) primary key auto_increment, 
  	tid char(11),
  	name char(64) not null,
  	leader_id int(11),
  	constraint fk_leader_employee foreign key(name) references leader(name)
);

注意

外键和关联的主表字段类型必须一致，但是允许size不一致，但是这样可能会出现无法预期的问题，不推荐这样做，曾经在使用django的ORM时，手写了migrations文件，导致出现类似情况，很大的坑。
外键字段可以是主表的多个字段（复合主键或者其他的情况都是被允许的，但是此时要保证关联字段和被关联字段要能对应起来）
特别要说明的是，作为其他表外键的主表字段必须是具有唯一约束（主键字段原生就自带 unique 属性，如果不是主键，那必须要手动的设置unique 当是外键是多个字段时，需要设置复合唯一）

删除外键

同删除主键类似，不同的是，每张表只有一个主键，因此不需要指定主键名，但是外键外键名是不虚指定的。

alter table tb_name drop foreign key fk_name;
-- fk_name  			外键约束名 也就是 constrain 后面的名字

唯一约束

什么是唯一约束

唯一约束就是要求字段中的值是唯一，特别的它允许为空，但只能出现一个空值。唯一约束可以确保一列不出现重复值，或者若干列不出现重复的组合。

创建唯一约束

1 字段定义时添加

create table leader(
  	id int(11) primary key auto_increment,
  	name char(64) unique,
);
-- name 字段唯一 ，不允许有重名的出现

2 字段定义完成后添加

create table leader(
  	id int(11) primary key auto_increment, 
  	name char(64),
  	constraint uq_name unique key(name)    -- 在这里写唯一约束 与主键类似
);

注意

唯一约束和主键约束的区别在于主键中不许出现null 但是唯一要求只能出现一个空值
一张表中可以有若个个唯一约束，但是只能存在一个主键约束

3 建表完成后添加

alter table tb_name add [constaint uq_name] unique key(uq_col_name)
-- tb_name 				要修改的表名
-- uq_name 				约束名<索引名>
-- uq_col_name 		声明唯一的字段名
-- 约束名 可以不写

删除唯一约束

alter table tb_name drop index fk_name;
-- tb_name 			表名
-- fk_name    	约束名<索引名>

默认值约束和非空约束

default 应该被称为默认值约束，他也是一种约束，作用和简单，指定某字段的默认值

创建默认值约束

1 字段中定义默认值/非空

create table user(
		id int(11) auto_increment primary key,
  	age int(4) default 18,
  	name char(64) not null
);

2 表建成后添加

alter table tb_name change col_name col_type[size] default <默认值>
-- 就是简单的修改表的结构语句
alter table user change col_name col_type[size] {default 默认值 | not null | null }

删除默认值/非空约束

-- 修改表结构 将默认值 设置为null
alter table tb_name change col_name col_type[size] default null;
-- 或者
-- 修改表结构 修改字段的类型
alter table tb_name modify col_name col_type[size];

检查约束

MySQL不支持检查约束，官方文档上说，即使设置了检查约束，但是mysql不会强制的要求这样做。创建之后在information_schema.table_constraints 找不到相关的记录。甚至他可能本身就不存在。

什么是检查约束

检查约束一般来说是为了满足用户实际的实体完整性而存在的。例如身高应该 high > 10 and high < 280

创建检查约束

1 创建表时添加

check <约束条件>
-- 例子
create table userinfo(
		id int(11) auto_increment primary key,
  	high int(4) default 160,
  	weight int(4) default 50,
  	check(heigh > 10 and heigh < 280)
);

2 创建完成后添加

alter table userinfo add constraint check_name check(约束条件)
-- 例如
alter table userinfo add constraint check_weight check(weight > 10 and weight < 300);

删除检查约束

alter table tb_name drop constraint check_name;
-- 例如
alter table userinfo drop constraint check_weight;
-- 值得注意的是，mysql 虽然说创建成功了，但是在information_schema 数据库的 table_constraints 表中并不能找到相关的信息。MySQL不支持检查约束。