【MySQL】4、表的各种约束、表的主外键关系

最新推荐文章于 2024-08-28 09:24:52 发布

浮生146

最新推荐文章于 2024-08-28 09:24:52 发布

阅读量270

点赞数 5

分类专栏： JavaSE学习文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/qq_56040271/article/details/140672812

版权

JavaSE学习专栏收录该内容

25 篇文章 0 订阅

订阅专栏

6. 表的各种约束

非空约束（比如：主键）、默认值约束、唯一约束（代表某个字段唯一、不重复）、主外键关系约束（最重要）

7. 表的主外键关系

7.1 基本理论

主外键关系，其实就是表与表之间的关系，在关系型数据库中，表与表之间的关系只有一种，那就是主外键关系。它代表的意义是一张表引用了另一张表的数据，所有，当我们对某张表进行CRUD操作时，要考虑其关联的那张表，具体如下：
1、创建有主外键关系的多张表作添加数据操作时，要先在主表中添加数据，再在子表中添加数据。
2、删除数据时，必须要先删除子表数据，再删除被子表引用的数据。
3、对主表进行修改操作时，也要考虑是否能够修改被引用的主表的那个字段，当然，一般主键字段是不允许被修改的，而具有唯一约束的字段则可以更改。
4、有两种情况可以设置外键，一是主表的字段是唯一约束字段，二是主表的相关字段是主键字段。

7.2 实际应用

创建student、classes、course、score四张表

主外键关系：

7.2.1 先创建表，再通过修改表结构来创建主外键关系

# 1、创建学生表
mysql> create table student(sid int primary key auto_increment,
    -> sname varchar(20),
    -> sex char(2),
    -> age int,
    -> addr varchar(20),
    -> cid int)charset utf8;
Query OK, 0 rows affected (0.01 sec)

# 2、向学生表添加数据
mysql> insert into student values(null,'张三','男',20,'上海',NUL
L),(null,'李四','男',21,'深圳',NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   20 | 上海 | NULL |
|   2 | 李四  | 男   |   21 | 深圳 | NULL |
+-----+-------+------+------+------+------+
2 rows in set (0.00 sec)

# 3、创建班级表
mysql> create table classes(cid int primary key auto_increment,
    -> cname varchar(20));
Query OK, 0 rows affected (0.01 sec)

# 4、向班级表添加数据
mysql> insert into classes(cname) value('bj2401'),('bj2402'),('b
j2403');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from classes;
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | bj2401 |
|   2 | bj2402 |
|   3 | bj2403 |
+-----+--------+
3 rows in set (0.00 sec)

# 5、为学生表添加外键约束
mysql> alter table student add constraint fk_01 foreign key(cid) references classes(cid);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
| cid   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 6、修改学生表的数据
mysql> update student set cid=1 where sid=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from student;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   20 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
+-----+-------+------+------+------+------+
2 rows in set (0.00 sec)

# 7、创建课程表
mysql> create table course(cid int primary key auto_increment,
    -> cname varchar(20));
Query OK, 0 rows affected (0.01 sec)

# 8、向课程表添加数据
mysql> insert into course(cname) value('c语言'),('Java'),('Python');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from course;
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | c语言  |
|   2 | Java   |
|   3 | Python |
+-----+--------+
3 rows in set (0.00 sec)

# 9、创建分数表
mysql> create table score(id int primary key auto_increment,
    -> sid int,
    -> cid int,
    -> score int);
Query OK, 0 rows affected (0.01 sec)

# 10、添加外键约束
mysql> alter table score
    -> add constraint fk_02 foreign key(sid) references student(sid), 
    -> add constraint fk_03 foreign key(cid) references course(cid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 11、向分数表添加数据
mysql> insert into score values(null,1,1,80),(null,1,2,75),(null,1,3,90);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(null,2,1,70),(null,2,2,85),(null,2,3,95);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(null,3,1,72),(null,3,2,82),(null,3,3,92);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from score;
+----+------+------+-------+
| id | sid  | cid  | score |
+----+------+------+-------+
|  1 |    1 |    1 |    80 |
|  2 |    1 |    2 |    75 |
|  3 |    1 |    3 |    90 |
|  4 |    2 |    1 |    70 |
|  5 |    2 |    2 |    85 |
|  6 |    2 |    3 |    95 |
|  7 |    3 |    1 |    72 |
|  8 |    3 |    2 |    82 |
|  9 |    3 |    3 |    92 |
+----+------+------+-------+
9 rows in set (0.00 sec)

优点： 不用考虑表的创建顺序。

7.2.2 如何删除有主外键关系的多张表

删除原则： 先删除子表，再删除主表

mysql> drop table score;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table course;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table classes;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

7.2.3 创建表结构的同时创建主外键关系

# 1、第一步：创建主表
mysql> create table classes(cid int primary key auto_increment,cname varchar(20))charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> create table course(cid int primary key auto_increment,cname varchar(20))charset utf8;
Query OK, 0 rows affected (0.01 sec)

# 2、第二步：创建学生表
mysql> create table student(sid int primary key auto_increment,
    -> sname varchar(20),
    -> sex char(2),
    -> age int,
    -> addr varchar(20),
    -> cid int,
    -> foreign key (cid) references classes(cid))charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
| cid   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 3、第三步：创建分数表
mysql> create table score (id int primary key auto_increment,
    -> sid int,
    -> cid int,
    -> score int,
    -> foreign key (sid) references student(sid),
    -> foreign key (cid) references course(cid)
    -> )charset utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc score;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| sid   | int(11) | YES  | MUL | NULL    |                |
| cid   | int(11) | YES  | MUL | NULL    |                |
| score | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

# 第四步：插入数据（先主表再子表）
## ① 先在course和classes两个表插入数据
mysql> insert into course values(null,'c语言'),(null,'java'),(null,'python');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from course;
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | c语言  |
|   2 | java   |
|   3 | python |
+-----+--------+
3 rows in set (0.00 sec)

mysql> insert into classes values(null,'bj2401'),(null,'bj2402'),(null,'bj2403');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from classes;
+-----+--------+
| cid | cname  |
+-----+--------+
|   1 | bj2401 |
|   2 | bj2402 |
|   3 | bj2403 |
+-----+--------+
3 rows in set (0.00 sec)

## ② 再在student表插入数据
mysql> insert into student values(null,'张三','男',22,'上海',1),(null,'李四','男',21,'深圳',2),(null,'王五','女',23,'杭州',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+-----+-------+------+------+------+------+
| sid | sname | sex  | age  | addr | cid  |
+-----+-------+------+------+------+------+
|   1 | 张三  | 男   |   22 | 上海 |    1 |
|   2 | 李四  | 男   |   21 | 深圳 |    2 |
|   3 | 王五  | 女   |   23 | 杭州 |    3 |
+-----+-------+------+------+------+------+
3 rows in set (0.00 sec)

## ③ 最后在score表中添加数据
mysql> insert into score values(null,1,1,85),(null,1,2,78),(null,1,3,90);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(null,2,1,75),(null,2,2,88),(null,2,3,80);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(null,3,1,55),(null,3,2,68),(null,3,3,70);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from score;
+----+------+------+-------+
| id | sid  | cid  | score |
+----+------+------+-------+
|  1 |    1 |    1 |    85 |
|  2 |    1 |    2 |    78 |
|  3 |    1 |    3 |    90 |
|  4 |    2 |    1 |    75 |
|  5 |    2 |    2 |    88 |
|  6 |    2 |    3 |    80 |
|  7 |    3 |    1 |    55 |
|  8 |    3 |    2 |    68 |
|  9 |    3 |    3 |    70 |
+----+------+------+-------+
9 rows in set (0.00 sec)

浮生146

关注

5
点赞
踩
10

收藏

觉得还不错? 一键收藏
打赏
0
评论
【MySQL】4、表的各种约束、表的主外键关系

主外键关系，其实就是表与表之间的关系，在关系型数据库中，表与表之间的关系只有一种，那就是主外键关系。3、对主表进行修改操作时，也要考虑是否能够修改被引用的主表的那个字段，当然，一般主键字段是不允许被修改的，而具有唯一约束的字段则可以更改。1、创建有主外键关系的多张表作添加数据操作时，要先在主表中添加数据，再在子表中添加数据。4、有两种情况可以设置外键，一是主表的字段是唯一约束字段，二是主表的相关字段是主键字段。2、删除数据时，必须要先删除子表数据，再删除被子表引用的数据。先删除子表，再删除主表。
复制链接

扫一扫