往事悠悠君莫问,回头。槛外长江空自流。
——王安石《南乡子》
数据库表的约束和表之间的关系
1. 数据库表的约束
约束的作用:
约束的种类
约束名 |
约束关键字 |
主键 |
primary key |
唯一 |
unique |
非空 |
not null |
外键 |
foreign key |
检查约束 |
check 注:mysql 不支持 |
1.1 主键约束
用来唯一标识数据库中的每一条记录
哪个字段应该作为表的主键?
通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
如:身份证,学号不建议做成主键
1.1.1 创建主键
创建主键关键字: primary key
主键的特点:
1 2 3 4 5 6
|
字段名 字段类型 primary key
alter table 表名 add primary key(字段名);
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| 创建表学生表 student, 包含字段(id, name, age)将 id做为主键 create table student ( id int primary key, name varchar(20), age int ) desc student;
insert into student values (1, '上官婉儿', 18);
insert into student values (1, '上官', 20);
select * from student;
insert into st5 values (null, '上官婉儿', 20);
|
1.1.2 删除主键
1 2 3 4
| alter table student drop primary key;
alter table student add primary key(id);
|
1.1.3 主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
auto_increment表示自动增长(字段类型必须是整数类型)
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| 创建表学生表 student, 包含字段(id, name, age)将 id做为主键 create table student ( id int primary key auto_increment, name varchar(20), age int )
insert into student (name,age) values ('小乔',18); insert into student (name,age) values ('大乔',20); insert into student values(null,'周瑜',35); select * from student;
|
1.1.3.1 修改自增长的默认值起始值
默认地 AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,请使用下列 SQL 语法
创建表的时候修改自增长起始值
1 2 3 4 5 6 7 8 9 10 11
| create table 表名 ( 列名 int primary auto_increment )auto_increment=起始值;
create table student ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000; insert into st4 values (null, '孔明');
|
创建好表以后修改起始值
1 2 3 4
| alter table 表名 auto_increment=起始值;
alter table student auto_increment = 2000; insert into student values (null, '刘备');
|
1.1.3.2 delete 和 truncate对自增长的影响
- delete 删除所有的记录之后,自增长没有影响。
- truncate 删除以后,自增长又重新开始。
1.2 唯一约束
什么是唯一约束?
就是表中某一列不能出现重复的值。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| create table student ( id int, name varchar(20) unique )
insert into student values (1, '张三'); select * from student;
insert into student values (2, '张三');
insert into student values (2, null); insert into student values (3, null);
|
1.3 非空约束
什么是非空约束?
就是某一列不能为 null。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| 字段名 字段类型 not null;
create table student ( id int, name varchar(20) not null, gender char(1) );
insert into student values (1,'张三疯','男'); select * from student;
insert into student values (2,null,'男');
|
1.2.1 非空约束默认值
什么是非空约束默认值?
字段名 字段类型 default 默认值;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| create table student ( id int, name varchar(20), address varchar(20) default '广州' )
insert into student values (1, '李四', default); select * from student;
insert into student (id,name) values (2, '李白');
insert into student values (3, '李四光', '深圳');
|
如果一个字段设置了非空与唯一约束,该字段与主键的区别?
- 主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。
- 自增长只能用在主键上
1.4 外键约束
1.4.1 单表的缺点
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| 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.4.2 解决方案
分成 2 张表,设置主表和从表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
|
create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) );
create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int )
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳'); select * from department;
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
1.4.3 外键约束
什么是外键?
在从表中与主表主键对应的那一列,如:员工表中dep_id
==主表==: 一方,用来约束别人的表
==从表==: 多方,被别人约束的表
1.4.3.1 创建外键约束
新建表时增加外键
1
| [constraint] [外键约束名称] foreign key (外键字段名) references 主表名(主键字段名);
|
已有表增加外键
1
| alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名);
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| 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) )
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;
insert into employee (name, age, dep_id) values ('老张', 18, 6);
|
1.4.3.2 删除外键
alter table 从表 drop foreign key 外键名称;
1 2 3 4 5 6 7
| alter table employee drop foreign key emp_depid_fk;
alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);
|
1.4.3.3 外键的级联
什么是级联操作?
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| select * from employee; select * from department;
update department set id=5 where id=2;
delete from department where id=1;
|
级联操作语法 |
描述 |
on update cascade |
级联更新,只能是创建表的时候创建级联关系, 更新主表中的主键,从表中的外键列也自动同步更新 |
on delete cascade |
级联删除 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| 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;
update department set id=10 where id=1; select * from employee; select * from department;
delete from department where id=2;
|
1.5 数据约束小结
约束名 |
关键字 |
说明 |
主键 |
primary key |
唯一,非空 |
默认 |
default |
如果一列没有值,使用默认值 |
非空 |
not null |
这一列必须有值 |
唯一 |
unique |
这一列不能有重复值 |
外键 |
foreign key |
主表中主键列,在从表中外键列 |
2. 表与表之间的关系
概念:
- 现实生活中,实体与实体之间肯定是有关系的,比如:爸爸和儿子,部门和员工,老师(喵喵)和学生(dd)等。那么我们在设计表的时候,就应该体现出表与表之间的这种关系!
表与表之间的三种关系 |
关系的维护 |
一对多:最常用的关系 部门和员工 |
主外键的关系 |
多对多:学生选课表 和 学生表, 一门课程可以有多个学生选择,一个学生选择多门课程 |
中间表,两个一对多 |
一对一:相对使用比较少。员工表 简历表, 公民表 护照表 |
特殊一对多,从表中的外键设置为唯一,从表中的主键又是外键 |
2.1 一对多
一对多(1 : n)
例如:班级和学生,部门和员工,客户和订单,分类和商品
**一对多建表原则: **
在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
2.2 多对多
多对多(m : n)
例如:老师和学生,学生和课程,用户和角色
多对多关系建表原则:
需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
2.3 一对一
一对一(1: 1)
在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则:
一对一的建表原则 |
说明 |
外键唯一 |
主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 unique |
外键是主键 |
主表的主键和从表的主键,形成主外键关系 |