Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

引入

1.什么是完整性约束, 为什么使用

为了规范数据格式, 在用户进行插入、修改、删除等操作时,DBMS(数据库管理系统(Data Base Management System))自动按照约束条件对数据进行监测, 使不符合规范的数据不能进入数据库, 以确保数据的完整性和唯一性

2.约束分类

表完整性约束条件与字段数据类型的宽度一样, 都是可选参数,分为以下几种:

完整性约束关键字 含义
NULL 标识字段值可以为空
NOT NULL 约束字段值不为空
DEAFAULT 设置字段值为默认值
UNIQUE KEY(UK) 约束字段的值唯一
PRIMARY KEY(PK) 设置字段为表的主键
FOREIGN KEY(FK) 设置字段为表的外键
AUTO_INCREAMENT 约束字段的值为自动递增
UNSIGNED 无符号
AEROFILL 使用 0 填充

一.null (空)

  • 被指定的字段的可以为空, 没有指定则默认约束条件就是null
create table t04(id int); 
insert t04 value();      # values() 也可以 value()
insert t04 value(null);  # 并且 into 可以省略
insert t04 value(1);

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

二.not null (非空)

  • 被指定字段的值不能为空
create table t05(name char(16) not null);
insert t05 value();
insert t05 value(null);
insert t05 value("shawn");

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

三.default (默认)

  • 被指定了default条件的字段在插入的值时, 无论null还是not null都可以插入空
  • 插入时不给赋值默认填入default指定的默认值
  • 注意 : 当已经制定了约束条件not nulldefault后面就不能指定null作为默认值
create table t06(name char not null default "shawn");
insert t06 value();
insert t06 value(null);

create table t07(name char not null dafault null);  # 直接报错

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

四.unique (唯一)

  • 被指定了unique的字段唯一, 两种唯一 :
    1. 单列唯一 : 一个字段唯一
    2. 联合唯一 : 多个字段联合后唯一

1.单列唯一

create table t07(id int unique,name char(16));
insert t07 value(1,"shawn"),(2,"song");
insert t07 value(1,"xing");  # 报错提示重复

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

2.联合唯一

create table t08(id int,ip varchar(16),port int,unique(ip,port));  # ip + port 联合单个可以重复, 联合不能相同
insert t08 value(1,"127.0.0.1",8090);
insert t08 value(2,"127.0.0.1",8091);
insert t08 value(3,"127.0.0.1",8090);  # 联合与id为1的相同, 报错

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

3.not null + unique

  • 不为空且唯一

五.primary key (主键)

主键约束(primary key)

1.什么是主键约束

  • primary key 约束唯一表示数据库表中的美条记录
  • 主键列不能为空(not null), 且必须唯一(unique), 效果上与(not null + unique一样)
  • 每个表都应该有一个主键, 并且每个表只能有一个主键

2.主键的作用

  • ’‘索引’'mysql提供的一种数据结构, 在mysql中称之为**“键”**
  • 它除了有约束效果之外, 它还是Innodb存储引擎组织数据的依据
  • 因为它类似于书的目录, 能够提升查询效率并且它也是建表的依据

3.未设置主键如何处理表

  • 首先Innodb从上往下搜索所有字段, 直到找到一个非空且唯一的字段, 该字段就会自动升级为主键
  • 如果表中既没有主键, 也没有非空且唯一的字段, 那么InnoDB会采用自己内部提供的一个隐藏字段作为主键, 此时提升查询效率的作用就无法享受了(隐藏字段看不见)

ps : InnoDB引擎表是基于B+树的索引组织表(IOT)(自行百度)

4.表的两种主键方式

  • 单列主键 : 单个字段的主键
  • 联合主键 : 多列做主键, 符合主键, 多个字段联合做主键

5.验证演示

  • 验证约束效果 not null + unique (非空且唯一)
create table t09(id int primary key);  # 设置主键
insert t09 value(1),(2);
insert t09 value(null);  # 插入 null 报错
insert t09 value(1);     # 插入id 1 报错
insert t09 value(2);     # 插入id 2 也报错

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

  • 验证不设置主键, 非空且唯一的字段自动升级主键
create table t10(
	id int not null unique,  # 设置 id 字段不为空,且唯一
	name char(16) not null,
	age int,
	sex char(3) not null
);
desc t10;  # 查看表结构

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

  • 单列主键演示
create table t11(
    id int primary key,
    name varchar(16)
);
desc t11;

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

  • 联合主键演示
create table t12(
    ip varchar(16),
    port int,
    primary key(ip,port)  # 设置 IP + port 联合主键
);
insert t12 value("127.0.0.1",8090);
insert t12 value("127.0.0.1",8091);  # 单个字段可以相同
insert t12 value("127.0.0.1",8090);  # 连个字段不能相同, 报错

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

6.主键总结

  • 建表必须制定一个主键, 一般将 id 字段设置成主键
  • 制定主键字段后, 该字段就非空且唯一(Innodb引擎的查找机制就是非空且唯一字段)

六.auto_increment (自增长)

1.自增长说明

  • 自增长字段的值默认从 1 开始, 每次递增 1
  • 自增长字段数据不可以重复
  • 如果插入数据时id字段指定为0null或未指定值,那么就把这个表当前的AUTO_INCREMENT值填到自增字段
  • 如果插入数据时id字段指定了具体的值,就直接使用语句里指定的值
  • 自增长字段必须是主键 (primary key)

2.使用场景

  • 编号过多, 人为的去设置和维护太麻烦, 可以添加自增字段解决

3.自增长演示

  • 不指定 id , 让其自动增长
create table t13(
    id int primary key auto_increment,
    name varchar(16)
);
insert t13(name) value("shawn"),("xing");

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

  • 指定 id, 则使用自己指定
insert t13 value(7,"xing"),(9,"hai");

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

insert t13(name) value("hello");  # 再次不指定 id 

Mysql完整性约束详解(字段唯一,非空,主键primary key,外键foreign key,自增长auto_increment)

4.删除表记录和清空表的区别

  • 对于自增字段, 在用delete删除后, 再插入值, 该字段仍按照删除前的位置继续增长(delete是一条一条删除记录)
上一篇:Oracle_fdw


下一篇:SQL FOREIGN KEY 约束:外键的用法大全