约束条件之主键

关键字：primary key

1. 定义

   主键（PRIMARY KEY）的完整称呼是“主键约束”。MySQL主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。

2. 强调

   在关系数据库，一个表中，只能有一个主键（primary key），有些数据库没有主键，系统报错。在MySQL数据库中，建立表时，可以有主键，也可以没有（推荐建立表时必须要有主键）。

3. 单从约束角度上而言主键等价于非空且唯一 not null unique

   mysql> create table t1(
       -> id int primary key,
       -> name varchar(32) default 'lzq'
       -> );
   Query OK, 0 rows affected (0.04 sec)
   
   mysql> insert into t1(name) values('jason');
   ERROR 1364 (HY000): Field 'id' doesn't have a default value
   
   mysql> insert into t1(id, name) values(1, 'jason');
   Query OK, 1 row affected (0.05 sec)
   
   mysql> insert into t1(id, name) values(1, 'jason');
   ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
   
   mysql> insert into t1(id, name) values(2, 'kevin');
   Query OK, 1 row affected (0.05 sec)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17

4. InnoDB存储引擎规定一张表必须有且只有一个主键

   • 如果既没有一个主键，也没有not null + unique的情况下，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(类似于书的目录)

   • 如果创建表的时候没有主键，但是有非空加唯一，那么InnoDB存储引擎会自动将该字段设置为主键(隐藏就意味着而无法使用 即无法加快数据查询)

     如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
     create table t2(
     	tid int not null unique,
     	pid int not null unique,
     	cid int not null unique
     );
     1
     2
     3
     4
     5
     6

5. 创建表的时候都应该有一个id字段 并且该字段应该作为主键

   create table t3(
   	id int primary key,
   	name varchar(32)
   );
   1
   2
   3
   4

6. 联合主键(多个字段组合 本质还是一个主键)

   create table t4(
   	id int,
   	name varchar(32),
   	pwd int,
   	primary key(id, pwd)
   );
   1
   2
   3
   4
   5
   6

7. 自增

   关键字：auto_increment

   该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)

   mysql> create table t2(
       -> id int primary key auto_increment,
       -> name varchar(32)
       -> );
   Query OK, 0 rows affected (0.11 sec)
   
   mysql> insert into t2(name) values('jason');
   Query OK, 1 row affected (0.05 sec)
   
   mysql> insert into t2(name) values('kevin');
   Query OK, 1 row affected (0.05 sec)
   
   mysql> select * from t2;
   +----+-------+
   | id | name  |
   +----+-------+
   |  1 | jason |
   |  2 | kevin |
   +----+-------+
   2 rows in set (0.00 sec)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20

   特点：

   • 自增的操作不会因为执行删除数据的操作而回退或者重置

     mysql> delete from t2 where id=2;
     Query OK, 1 row affected (0.05 sec)
     
     mysql> insert into t2(name) values('oscar');
     Query OK, 1 row affected (0.05 sec)
     
     mysql> select * from t2;
     +----+-------+
     | id | name  |
     +----+-------+
     |  1 | jason |
     |  3 | oscar |
     +----+-------+
     2 rows in set (0.00 sec)
     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14

   • 如果非要重置主键 需要格式化表

     truncate 表名;  # 删除表数据并重置主键值
     1

     mysql> truncate t2;
     Query OK, 0 rows affected (0.08 sec)
     
     mysql> select * from t2;
     Empty set (0.00 sec)
     1
     2
     3
     4
     5

   优点：

   （1）数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利；
   （2）数字型，占用空间小，易排序，在程序中传递也方便；
   （3）如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题。

约束条件之外键

• 定义

  外键约束（Foreign Key）即数据库中两个数据表之间的某个列建立的一种联系。

• 作用

  MySQL通过外键约束的引入，可以使得数据表中的数据完整性更强；保证数据的一致性

• 强调

  • 与外键关联的主表的字段必须设置为主键
  • 要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

外键约束的创建

• 创建外键约束的条件

  MySQL数据库外键的创建，需要满足以下四个条件，否则会被MySQL数据库拒绝：

  • 创建外键的表和列存在
  • 组成外键的列存在索引
  • 必须指定数据表的引擎为InnoDB
  • 外键字段和关联字段，数据类型必须一致

• 外键关系

  外键是用来记录表与表之间的数据关系，关系一共有四种

  1. 一对一
  2. 一对多
  3. 多对多
  4. 没有关系

• 在创建数据表时创建外键约束

  在创建数据表时创建外键约束，只需要在创建数据表的create语句后面，使用foreign key关键字指定本表的外键字段，使用reference关键字指定关联表的关联字段，并且明确约束行为即可。
  创建外键约束的SQL语句示例如下：

  create table course(
  	id int primary key auto_increment,
  	name varchar(32),
  	stu_id int,
  	foreign key(stu_id) references student(id)
  	on update cascade
  	on delete cascade
  );
  
  create table student(
  	id int primary key auto_increment,
  	name varchar(32)
  );
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

• foreign key的约束效果

  1.创建表的时候 应该先创建被关联表(没有外键字段的表)
  2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
  外键字段填入的值只能是被关联表中已经存在的值
  3.被关联字段无法修改和删除

• 级联更新、级联删除

  理解

  被关联数据一旦变动 关联的数据会随之同步变动

  on update cascade  # 级联更新
  on delete cascade  # 级联删除
  1
  2

扩展:

在实际工作中 很多时候可能并不会使用外键
    因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
    自己通过写SQL 建立代码层面的关系
1
2
3
4

表关系之多对多

• 举例说明

  以书籍表和作者表为例说明

  1.先站在书籍的层面上
  问：一本书籍可以对应多个作者吗？
  答：可以

  2.再站在作者的层面上
  问：一个作者可以对应多个书籍吗？
  答：可以

  总结：如果两边层面都可以，那么关系则为多对多

针对多对多表关系，外键字段不能建在任意一方!!!应该创建第三方表专门用来存放对应关系

create table book(
     id int primary key auto_increment,
     title varchar(32)
);
create table author(
     id int primary key auto_increment,
     name varchar(32)
);
create table book2author(
   	id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 级联更新 
    on delete cascade, # 级联删除
    author_id int,
    foreign key(author_id) references author(id) 
    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

如果不创建第三方表来存放对应关系的话，会出现双方需要的字段都还未创建

表关系之一对一

• 举例说明

  以用户表和用户详情表为例说明

  1.先站在用户的层面上
  问：单个用户可以对应多个用户详情吗？
  答：不可以

  2.再站在用户详情表的层面上
  问：单个用户详情可以对应多个用户吗？
  答：不可以

  总结：如果两边层面都不可以，那么关系则为一对一或者是没有关系

针对一对一的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询

create table User(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others'),
    detail_id int unique,  
    foreign key(detail_id) references UserDetail(id) 
    on update cascade   
    on delete cascade
);
create table UserDetail(
    id int primary key auto_increment,
    phone bigint,
    age int
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

表关系之一对多

• 举例说明

  以学生表和部门表为例说明

  1.先站在学生的层面上
  问：一个学生可以对应多个部门吗？
  答：不可以

  2.再站在部门的层面上
  问：单个部门可以对应多个学生吗？
  答：可以

  总结：如果一边层面可以，另一边层面不可以，那么关系则为一对多

针对一对多的表关系 外键字段建在多的表里

其实一对一跟一对多的区别就在于一个唯一性的关键字(unique)

create table student(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others'),
    dep_id int,  
    foreign key(dep_id ) references dep(id) 
    on update cascade   
    on delete cascade
);
create table dep(
    id int primary key auto_increment,
	dep_desc varchar(32)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

今日作业

书籍表与出版社表(多对多)

mysql> create table book(
    -> id int primary key auto_increment,
    -> book_name varchar(32)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> create table press(
    -> id int primary key auto_increment,
    -> press_name varchar(32)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create table book2press(
    -> id int primary key auto_increment,
    -> book_id int,
    -> foreign key(book_id) references book(id)
    -> on update cascade
    -> on delete cascade,
    -> press_id int,
    -> foreign key(press_id) references press(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

学生表与班级表(一对多)

mysql> create table student(
    -> stu_id int primary key auto_increment,
    -> stu_name varchar(32),
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table class(
    -> id int primary key auto_increment,
    -> class_name varchar(32)
    -> );
Query OK, 0 rows affected (0.08 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

老师表与课程表(一对一)

mysql> create table course(
    -> id int primary key auto_increment,
    -> course_name varchar(32)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create table teacher(
    -> tea_id int primary key auto_increment,
    -> tea_name varchar(32),
    -> course_id int unique,
    -> foreign key(course_id) references course(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

课程表与班级表(多对多)

mysql> create table course1(
    -> course_id int primary key auto_increment,
    -> course_name varchar(32)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> create table class1(
    -> class_id int primary key auto_increment,
    -> class_name varchar(32)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table course2class(
    -> id int primary key auto_increment,
    -> cou_id int,
    -> foreign key(cou_id) references course1(course_id)
    -> on update cascade
    -> on delete cascade,
    -> cla_id int,
    -> foreign key(cla_id) references class1(class_id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.12 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

服务器表与应用程序表(一对多)

mysql> create table server(
    -> pid int primary key auto_increment,
    -> server_name varchar(32)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> create table client(
    -> id int primary key auto_increment,
	-> client_name varchar(32),
	-> server_pid int,
	-> foreign key(server_pid) references server(pid)
	-> on update cascade
	-> on delete cascade
    -> );
Query OK, 0 rows affected (0.11 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15