MySQL主键、MySQL外键、表关系

MySQL主键、MySQL外键、表关系

一、约束条件之主键

1、单从约束角度上而言主键等价于非空且唯一(not null、unique)。

create table t1(
id int primary key,
name varchar(32)
);

代码演示：

mysql> use db2;
Database changed
mysql> create table t1(
    -> id int primary key,
    -> name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 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.00 sec)

mysql> insert into t1(id,name) values(1,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
+----+-------+
1 row in set (0.00 sec)

2、InnoDB存储引擎规定一张表必须有且只有一个主键。

  • 如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询)。

  • 如果创建的表中没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设置为主键。当有多个非空且唯一的字段，从上往下的第一个字段设置为主键。

create table t2(
nid int not null unique,
sid int not null unique,
uid int not null unique,
name varchar(32)
);

代码演示：

mysql> create table t2(
    -> nid int not null unique,
    -> sid int not null unique,
    -> uid int not null unique,
    -> name varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| nid   | int(11)     | NO   | PRI | NULL    |       |
| sid   | int(11)     | NO   | UNI | NULL    |       |
| uid   | int(11)     | NO   | UNI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3、创建表的时候都应该有一个'id'字段，并且该字段作为主键。(uid、sid、pid、cid、id)

# 单列主键
create table t3(
id int primary key,
name varchar(32),
age int
);

# 联合主键
create table t4(
user_id int,
good_id int,
primary key(user_id,good_id)
);

代码演示：

mysql> create table t3(
    -> id int primary key,
    -> name varchar(32),
    -> age int);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> create table t4(
    -> user_id int,
    -> good_id int,
    -> primary key(user_id,good_id));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| user_id | int(11) | NO   | PRI | 0       |       |
| good_id | int(11) | NO   | PRI | 0       |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

二、auto_increment自增

auto_increment约束条件不能单独使用，主要配合主键一起使用。

create table t5(
id int auto_increment,
name varchar(32)
);
'''there can be only one auto column and it must be defined as a key'''

create table t6(
id int primary key auto_increment,
name vaechar(32)
);

代码演示：

mysql> create table t6(
    -> id int primary key auto_increment,
    -> name varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t6(name) values('jason'),('jack'),('kevin'),('oscar');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | jack  |
|  3 | kevin |
|  4 | oscar |
+----+-------+
4 rows in set (0.00 sec)

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

    mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | jack  |
|  3 | kevin |
|  4 | oscar |
+----+-------+
4 rows in set (0.00 sec)

mysql> delete from t6 where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | jack  |
|  3 | kevin |
+----+-------+
3 rows in set (0.00 sec)

mysql> insert into t6(name) values('tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | jack  |
|  3 | kevin |
|  5 | tom   |
+----+-------+
4 rows in set (0.00 sec)

mysql> delete from t6;
Query OK, 4 rows affected (0.00 sec)

mysql> insert into t6(name) values('rose');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+----+------+
| id | name |
+----+------+
|  6 | rose |
+----+------+
1 row in set (0.00 sec)

  • 重置主键，需要格式化表，truncate 表名;：删除表数据并重置主键值

    mysql> select * from t6;
+----+------+
| id | name |
+----+------+
|  6 | rose |
+----+------+
1 row in set (0.00 sec)

mysql> truncate t6;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t6;
Empty set (0.00 sec)

mysql> insert into t6(name) values('jason'),('rose'),('jack'),('oscar');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | rose  |
|  3 | jack  |
|  4 | oscar |
+----+-------+
4 rows in set (0.00 sec)

补充说明：

解决：为数据表设置自动增长，不会使插入的值存在而报错，针对某个字段

1.一个表中只能有一个自动增长的字段
2.该字段的数据类型是整数类型
3.该字段必须定义为键，如unique key、primary key
4.若为自动增长的字段插入null、0、default或在插入时省略该字段，该字段就会使用自动增加值
5.若插入一个具体的值，则不会使用自动增长值；
6.自动增长值从1开始自增，每次自增1
7.若插入的值大于自动增长的值，则消磁插入的自动增长值会自动使用最大值加1
8.若加入的值小于自动增长值，则不会对自动增长至产生影响
9.使用delete删除表记录，自动增长值不会减小或填补空缺

mysql> create table t7(
    -> id int primary key auto_increment,
    -> name varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values(null,'jason');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 values(3,'rose');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 values(0,'jack');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  3 | rose  |
|  4 | jack  |
+----+-------+
3 rows in set (0.00 sec)

为现有的表修改或者删除自动增长

1.修改自动增长值为n
alter table 表名 auto_increment=n;

2.删除自动增长值
alter table 表名 modify id int unsigned;

3.重新为id添加自动增长值
alter table 表名 modify id int unsigned auto_increment;

PS：
1）自动增长删除并重新添加后，自动增长的初始值会已支付设为该列现有的最大值加1。
2）在修改自动增长值时，修改的值若小于该列现有的最大值，则修改不会生效。

三、约束条件之外键

1、外键前戏

1.若需要创建一张员工表：

id name gender dep_name dep_desc
1 jason male 学习部 检查学习
2 kevin male 纪检部 检查学风
3 oscar male 宿管部 检查卫生
4 jack male 纪检部 检查学风
5 lili female 宿管部 检查卫生
6 tom female 学习部 检查学习 
上述表的缺陷：
1）表结构不清晰，到底是员工表还是部门表
2）字段数据反复存取，浪费存储空间
3）表的扩展性极差，牵一发动全身，效率极低

2.优化操作，拆表：

员工表：

id name gender
1 jason male
2 kevin male
3 oscar male
4 jack male
5 lili female
6 tom female

部门表：

id dep_name dep_desc
1 学习部 检查学习
2 纪检部 检查学风
3 宿管部 检查卫生

3.添加一个部门编号字段填写部门数据的主键值，外键字段，专门用于记录与表之间的数据关系

id name gender dep_id
1 jason male 1
2 kevin male 2
3 oscar male 3
4 jack male 2
5 lili female 3
6 tom female 1

2、外键字段的创建

外键的字段是用来记录表与表之间数据的关系，而数据的关系有四种：
一对多关系
多对多关系
一对一关系
没有关系

外键表示一个表中的一个字段被另一个表中的一个字段引用，外键对相关表中的数据造成限制。

外键约束主要用来维护两个表之间数据的一致性。表的外键就是另一个表的主键，外键将两表联系起来。要删除一张表中的主键必须要确保其它表中没有一个外键和它相关联。

定义外键：
1）主表必须先创建
2）必须为主表定义主键
3）主键不能包含控制，但允许外键出现空值
4）外键中列的数目必须和主表中主键列的个数相同
5）外键中列的数据类型必须和主表中主键列的数据类型相同

3、创建外键

1.先写普通字段
2.然后再写外键字段

create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);

create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);

'''
1.创建表的时候需要先创建被关联的表(没有外键)，然后在创建关联表(有外键)
2.插入表数据的时候，针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除(有点不好，操作限制性太强)
'''

代码演示：

mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> gender enum('male','female','others') default 'male',
    -> dep_id int,
    -> foreign key(dep_id) references dep(id));
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> create table dep(
    -> id int primary key auto_increment,
    -> dep_name varchar(32),
    -> dep_desc varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> gender enum('male','female','other') default 'male',
    -> dep_id int,
    -> foreign key(dep_id) references dep(id));
Query OK, 0 rows affected (0.03 sec)

mysql> desc emp;
+--------+-------------------------------+------+-----+---------+----------------+
| Field  | Type                          | Null | Key | Default | Extra          |
+--------+-------------------------------+------+-----+---------+----------------+
| id     | int(11)                       | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32)                   | YES  |     | NULL    |                |
| gender | enum('male','female','other') | YES  |     | male    |                |
| dep_id | int(11)                       | YES  | MUL | NULL    |                |
+--------+-------------------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc dep;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(32) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> insert into emp(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |   NULL |
+----+-------+--------+--------+
1 row in set (0.00 sec)

mysql> insert into emp(name,dep_id) values('kevin',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> insert into dep(dep_name,dep_desc) values('学习部','检查学习'),('纪检部','检查学风'),('宿管部','检查卫生');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 学习部    | 检查学习     |
|  2 | 纪检部    | 检查学风     |
|  3 | 宿管部    | 检查卫生     |
+----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> insert into emp(name,dep_id) values('kevin',2),('oscar',3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |   NULL |
|  3 | kevin | male   |      2 |
|  4 | oscar | male   |      3 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

mysql> truncate emp;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into emp(name,dep_id) values('kevin',1),('oscar',2),('jason',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | kevin | male   |      1 |
|  2 | oscar | male   |      2 |
|  3 | jason | male   |      3 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

mysql> insert into emp(name,emp_id) values('tom',4);
ERROR 1054 (42S22): Unknown column 'emp_id' in 'field list'
mysql> insert into emp(name,dep_id) values('tom',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> delete from dep where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db2`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> update dep set id=8 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db2`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

4、级联更新、级联删除

级联更新、级联删除：被关联数据一旦变动，关联的数据同步变动
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);

create table emp1(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade  # 级联更新
on delete cascade  # 级联删除
);

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

代码演示：

mysql> create table dep1(
    -> id int primary key auto_increment,
    -> dep_name varchar(32),
    -> dep_desc varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table emp1(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> gender enum('male','female','others') default 'male',
    -> dep_id int,
    -> foreign key(dep_id) references dep1(id)
    -> on update cascade
    -> on delete cascade);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into dep1(dep_name,dep_desc) values('学习部','检查学习'),('纪检部','检查学风'),('宿管部','检查卫生');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep1;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 学习部    | 检查学习     |
|  2 | 纪检部    | 检查学风     |
|  3 | 宿管部    | 检查卫生     |
+----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> insert into emp1(name,dep_id) values('jason',1),('oscar',2),('kevin',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | oscar | male   |      2 |
|  3 | kevin | male   |      3 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

mysql> update dep1 set id=200 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep1;
+-----+-----------+--------------+
| id  | dep_name  | dep_desc     |
+-----+-----------+--------------+
|   1 | 学习部    | 检查学习     |
|   3 | 宿管部    | 检查卫生     |
| 200 | 纪检部    | 检查学风     |
+-----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | oscar | male   |    200 |
|  3 | kevin | male   |      3 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

mysql> delete from dep1 where id=200;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dep1;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 学习部    | 检查学习     |
|  3 | 宿管部    | 检查卫生     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  3 | kevin | male   |      3 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)

四、表表关系之一对多(换位思考)

针对员工表和部门表判断数据关系
1.站在员工的角度
问：一条员工数据能否对应多条部门数据
翻：一名员工能否属于多个部门
答：不可以
2.站在部门表的角度
问：一条部门数据能否对应多条员工数据
翻：一个部分能否拥有多个员工
答：可以

换位思考之后的答案为一个可以一个不可以
>>> 那么表关系就是'一对多'
>>> 部门以一，员工是多
>>> 针对'一对多'的关系，外键字段建在多的一方
注意：没有‘多对一’，统称为'一对多'

create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);

create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','other') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);

代码演示：

mysql> create table dep1(
    -> id int primary key auto_increment,
    -> dep_name varchar(32),
    -> dep_desc varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table emp1(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> gender enum('male','female','others') default 'male',
    -> dep_id int,
    -> foreign key(dep_id) references dep1(id)
    -> on update cascade
    -> on delete cascade);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into dep1(dep_name,dep_desc) values('学习部','检查学习'),('纪检部','检查学风'),('宿管部','检查卫生');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep1;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 学习部    | 检查学习     |
|  2 | 纪检部    | 检查学风     |
|  3 | 宿管部    | 检查卫生     |
+----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> insert into emp1(name,dep_id) values('jason',1),('oscar',2),('kevin',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | oscar | male   |      2 |
|  3 | kevin | male   |      3 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

五、关系之多对多(换位思考)

以书籍表和作者表为例
1.先站在书籍表的角度
问：一条书籍数据能否对应多条作者数据
翻：一本书籍能否多个作者编写
答：可以
2.站在作者的角度
问：一条作者数据能否对应多条书籍数据
翻：一个作者能否写多本书籍
答：可以

换位思考之后的答案为两边都可以
>>> 那么表关系就是'多对多'
>>> 针对'多对多'的关系，外键字段不能建在任意一方

开始：
create table book(
id int primary key auto_increment,
title varchar(32),
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);

create table author(
id int primary key auto_increment,
name varchar(32),
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
# 报错，无论哪张表现执行，都会提示有个字段不存在

需要单独开设第三张表，存储数据关系
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 book_author(
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
);

代码演示：

mysql> create table book(
    -> id int primary key auto_increment,
    -> title varchar(32),
    -> auto_id int,
    -> foreign key(author_id) references author(id)
    -> on update cascade
    -> on delete cascade);
ERROR 1072 (42000): Key column 'author_id' doesn't exist in table
mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> book_id int,
    -> foreign key(book_id) references book(id)
    -> on update cascade
    -> on delete cascade);
ERROR 1215 (HY000): Cannot add foreign key constraint

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

mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> create table book_author(
    -> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into book_author(book_id,author_id) values(1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`book_author`, CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
# 两张表啥数据都没有，添加肯定报错

六、表关系之一对一(换位思考)

以用户表和用户详情表
1.先站在用户表的角度
问：一条用户数据能否对应多条多个用户详情表内的数据
翻：一个用户能否有多个详情的信息
答：不可以
2.站在用户详情表的角度
问：一条用户详情表内的数据能否对应多条用户数据
翻：一个详情信息能否有多个用户
答：不可以

换位思考之后的答案为两边都不可以
>>> 那么先考虑是不是没有关系
>>> 如果有关系那么肯定是'一对一'
>>> 针对'一对一'的表关系，外键字段建在任何一张表都可以，但是建议建在查询频率较高的表中，便于后续查询

create table userDetail(
id int primary key auto_increment,
phone bigint
);

create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userDeatil(id)
on update cascade
on delete cascade
);

代码演示：

mysql> create table userdetail(
    -> id int primary key auto_increment,
    -> phone bigint);
Query OK, 0 rows affected (0.02 sec)

mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> detail_id int unique,
    -> foreign key(detail_id) references userdetail(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.01 sec)

作业

1.服务器表与应用程序表
服务器：一个服务器可以服务多个应用程序
应用程序：一个应用程序不可以在多个服务器上
>>> '一对多'

create table the_server(
id int primary key auto_increment,
name varchar(32)
);

create table program(
id int primary key auto_increment,
name varchar(32),
server_id int,
foreign key(server_id) references the_server(id)
on update cascade
on delete cascade
);


2.课程表与班级表
课程表：一个课程可以对应多个班级，不同的班级可以让同一个课程
班级表：一个班级可以对应多个课程，一个班级可以上多门课程
>>> '多对多'

create table class(
id int primary key auto_increment,
name varchar(32)
);

create table course(
id int primary key auto_increment,
name varchar(32)
);

create table class_course(
id int primary key auto_increment,
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade,
course_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
);


3.学生表与班级表
学生表：一个学生对应一个班级，一个学生只能在一个班级
班级表：一个班级对应多个学生，一个班级上有多个学生
>>> '一对多'

create table class(
id int primary key auto_increment,
name varchar(32)
);

create table student(
id int primary key auto_increment,
name varchar(32),
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);


4.老师表与课程表
老师表：一个老师对应多个课程，一个老师可以教授多门课程
课程表：一门课程对应多个老师，一门课程通常是由多个老师教授的，如数学小组的老师都是教授数学
>>> '多对多'

create table teacher(
id int primary key auto_increment,
name varchar(32)
);

create table course(
id int primary key auto_increment,
name varchar(32)
);

create table teacher_course(
id int primary key auto_increment,
teacher_id int,
foreign key(teacher_id) references teacher(id)
on update cascade
on delete cascade,
course_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
);


5.书籍表与出版社
书籍表：一个书籍对应多个出版社，一本书可以由多个出版社打印出版
出版社：一个出版社对应多本书籍，一个出版社可以打印出版多本书籍
>>> '多对多'

create table book(
id int primary key auto_increment,
name varchar(32)
);

create table press(
id int primary key auto_increment,
name varchar(32)
);

create table book_press(
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
);

1.服务器表与应用程序表
mysql> use db2;
Database changed
mysql> create table the_server(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table program(
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> server_id int,
    -> foreign key(server_id) references the_server(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.02 sec)


2.课程表与班级表
mysql> create table class(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.02 sec)

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

mysql> create table class_course(
    -> id int primary key auto_increment,
    -> class_id int,
    -> foreign key(class_id) references class(id)
    -> on update cascade
    -> on delete cascade,
    -> course_id int,
    -> foreign key(course_id) references course(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.02 sec)


3.学生表与班级表
mysql> create table class(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(
    -> id int primary key auto_increment,
    -> 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.03 sec)


4.老师表与课程表
mysql> create table teacher(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.02 sec)

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

mysql> create table teacher_course(
    -> id int primary key auto_increment,
    -> teacher_id int,
    -> foreign key(teacher_id) references teacher(id)
    -> on update cascade
    -> on delete cascade,
    -> course_id int,
    -> foreign key(course_id) references course(id)
    -> on update cascade
    -> on delete cascade
    -> );
Query OK, 0 rows affected (0.03 sec)


5.书籍表与出版社
mysql> create table book(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> create table book_press(
    -> 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.01 sec)
posted @   努力努力再努力~W  阅读(80)  评论(0)  编辑  收藏  举报
相关博文：
·  正反向查询进阶操作、聚合查询、分组查询、F与Q查询、ORM查询优化、事物操作、模型层常见字段、ORM常见字段参数、多对多三种创建方式
·  表查询数据准备及测试、ORM常见查询关键字、双下划线查询、查看ORM底层SQL语句、ORM外键字段创建、外键字段数据操作、正反向概念、基于对象的跨表查询(子查询)、基于双下划线的跨表查询(连表操作)
·  【MySQL】第3回 主键与外键
·  MySQL数据库学习笔记
·  约束条件之主键、外键、表关系之一对一、一对多、多对多
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~
点击右上角即可分享
微信分享提示
SQL AI 助手