MySQL总结(七)数据库表的约束-详解

MySQL总结(七)数据库表的约束-详解

数据库表的约束

1. 数据库约束的概述

1.1 约束的作用:

对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

1.2 约束种类:

约束名 约束关键字 特点
主键 primary key 保证以该列字段查询,查出唯一数据
唯一 unique 保证该列数据的唯一性
非空 not null 保证该列数据不能为null
外键 foreign key 设计多表,从表的数据,依赖于主表;
默认 default 如果该列数据不存数据,那么有一个默认值; timestamp如果添加数据时,为null,那么默认当前系统时间
检查约束 check 注意:mysql不支持,后期在oracle总结中进行讲解 插入或修改数据时,sql检查是否符合条件

2. 主键约束

2.1 主键的作用

用来唯一标识数据库中的每一条记录

2.2 哪个字段应该作为表的主键?

通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。

主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。

如: 身份证,学号不建议做成主键

2.3 创建主键

  • 关键字:primary key

  • 特点:

    • 非空 not null
    • 唯一 unique
  • 创建方式

/* 
在创建表的时候给字段添加主键
-- 格式: 字段名 字段类型 primary key
*/
/*
在已有表中添加主键
-- alter table 表名 add primary key(字段名);
*/
-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
  id int primary key, -- id 为主键
  name varchar(20),
  age int
);
desc st5;

-- 插入重复的主键值
insert into st5 values (1, "关羽", 30);
-- 错误代码: 1062 Duplicate entry "1" for key "PRIMARY"
insert into st5 values (1, "关云长", 20);

select * from st5;

-- 插入 NULL 的主键值, Column "id" cannot be null
insert into st5 values (null, "关云长", 20);

2.4 删除主键

-- 删除 st5 表的主键
alter table st5 drop primary key;

-- 添加主键
alter table st5 add primary key(id);

2.5 主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

auto_increment 表示自动增长(字段类型必须是整数类型)

-- 插入数据
insert into st6 (name,age) values ("小乔",18);
insert into st6 (name,age) values ("大乔",20);
-- 另一种写法
insert into st6 values(null,"周瑜",35);
select * from st6;

2.6 修改自增长的默认值起始值

默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法

  • 创建表时指定起始值
-- 格式:
create table 表名(
  列名 int primary key auto_increment
) auto_increment = 起始值;
-- 指定起始值为 1000
create table st4 (
  id int primary key auto_increment,
  name varchar(20)
) auto_increment = 1000;

insert into st4 values (null, "孔明");
select * from st4;
  • 创建好表后修改起始值
-- 格式:
alter table 表名 auto_increment = 起始值;
alter table st4 auto_increment = 2000;
insert into st4 values (null, "刘备");

2.7 delete 和truncate 对自增长的影响

  • delete:删除所有的记录之后,对自增长没有影响。

  • truncate:删除以后,自增长又重新开始。

3.唯一约束

什么是唯一约束:表中某一列不能出现重复的值

3.1 唯一约束的基本格式

字段名 字段类型 unique

3.2 实现唯一约束

-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
create table st7(
  id int,
  name varchar(20) unique
);

-- 添加一个学生
insert into st7 values(1,"张三");

select * from st7;
-- Duplicate entry "张三" for key "name"
insert into st7 values (2, "张三");
  • 插入多个 null 会怎样?
insert into st7 values (2, null);
insert into st7 values (3, null);
select * from st7;

4.非空约束

什么是非空约束:某一列不能为null。

4.1 非空约束的基础语法格式

字段名 字段类型 not null
-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
create table st8 (
  id int,
  name varchar(20) not null,
  gender char(1)
);

-- 添加一条记录其中姓名不赋值
insert into st8 values (1,"张三疯","男");
select * from st8;

-- Column "name" cannot be null
insert into st8 values (2,null,"男");

5.默认值

什么是默认值:某列没有添加数据时,使用默认数据

字段名 字段类型 default 默认值
-- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州
create table st9 (
  id int,
  name varchar(20),
  address varchar(20) default "广州"
);

-- 添加一条记录,使用默认地址
insert into st9 values (1, "李四", default);
select * from st9;

insert into st9 (id,name) values (2, "李白");

-- 添加一条记录,不使用默认地址
insert into st9 values (3, "李四光", "深圳");
  • 如果一个字段设置了非空与唯一约束,该字段与主键的区别?
    • 1)主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。
    • 2)自增长只能用在主键上。

6.外键约束

6.1 单表的缺点

  • 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(30),
  age INT,
  dep_name VARCHAR(30),
  dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("张三", 20, "研发部", "广州");
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("李四", 21, "研发部", "广州");
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("王五", 20, "研发部", "广州");
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("老王", 20, "销售部", "深圳");
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("大王", 22, "销售部", "深圳");
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ("小王", 18, "销售部", "深圳");
  • 以上数据表的缺点:
      1. 数据冗余
      1. 后期还会出现增删改的问题

6.2 解决方案

-- 解决方案:分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
create table department(
  id int primary key auto_increment,
  dep_name varchar(20),
  dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int -- 外键对应主表的主键
);
-- 添加 2 个部门
insert into department values(null, "研发部","广州"),(null, "销售部", "深圳");
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ("张三", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("李四", 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("王五", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("老王", 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("大王", 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("小王", 18, 2);
select * from employee;
  • 问题:

当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id

  • 目标: 需要约束 dep_id 只能是 department 表中已经存在 id

  • 解决方案: 使用外键约束

6.3 什么是外键约束

  • 什么是外键:在从表中与主表对应的那一列。如:员工表中的dep_id
  • 主表:一方,用来约束别人的表
  • 从表:多方,被别人约束的表

6.4 创建约束的语法

  • 新建表时增加外键:
[constraint] [外键约束名称] foreign key (外键字段名) references 主表名(主键字段名)
  • 已有表增加外键:
alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名)
  • 具体操作:
-- 1) 删除副表/从表 employee
drop table employee;
-- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方,从表
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int, -- 外键对应主表的主键
  -- 创建外键约束
  constraint emp_depid_fk foreign key (dep_id) references department(id)
);
-- 3) 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ("张三", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("李四", 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("王五", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("老王", 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("大王", 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("小王", 18, 2);
select * from employee;
-- 4) 部门错误的数据添加失败
-- 插入不存在的部门
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ("老张", 18, 6);

6.5 删除外键

alter table 从表 drop foreign key 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

-- 在 employee 表存在的情况下添加外键
--      从表employee          约束名称emp_depid_fk       从表字段dep_id        主表department 主表字段id
alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);

6.6 外键的级联

  • 出现新问题:
select * from employee;
select * from department;
-- 要把部门表中的 id 值 2,改成 5,能不能直接更新呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 要删除部门 id 等于 1 的部门, 能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;
  • 什么是级联操作:

在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作

级联操作语法 描述
ON UPDATE CASCADE 级联更新,只能是创建表的时候创建级联关系。更新主表中的主键,从表中的外键列也自动同步更新
ON DELETE CASCADE 级联删除
-- 删除 employee 表,重新创建 employee 表,添加级联更新和级联删除
drop table employee;
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int, -- 外键对应主表的主键
  -- 创建外键约束
  constraint emp_depid_fk foreign key (dep_id) references
  department(id) on update cascade on delete cascade
)
-- 再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ("张三", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("李四", 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("王五", 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ("老王", 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("大王", 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ("小王", 18, 2);
-- 删除部门表?能不能直接删除?
drop table department;
-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- 删除部门号是 2 的部门
delete from department where id=2;

7. 数据约束小结

约束名 约束关键字 描述
主键 primary key 唯一,非空
唯一 unique 这一列不能有重复值
非空 not null 这一列必须有值
外键 foreign key 主表中的主键列,在从表中的外键列
默认 default 如果一列没有值,使用默认值
检查约束 check 注意:mysql不支持,后期在oracle总结中进行讲解 插入或修改数据时,sql检查是否符合条件

OK,这篇就到这里