数据库05 约束外键多表

首先，创建一张新表

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, '销售部', '深圳');

但是此表中部门名称与部门地址重复次数较多，产生冗余，因此可以进行改进

-- 解决方案：

分成 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);

若是产品研发完毕，需要撤除研发部，那么张三，李四，王五该如何？

可通过外键约束解决

此时再删除研发部则会报错

* 外键约束：foreign key（fk）,让表于表产生关系，从而保证数据的正确性。
       1. 在创建表时，可以添加外键
           * 语法：
               create table 表名(
                   ....
                   外键列
                   constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
               );

2. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

       3. 创建表之后，添加外键
           ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);


       4. 级联操作
           1. 添加级联操作
               语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
                       FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
           2. 分类：
               1. 级联更新：ON UPDATE CASCADE
               2. 级联删除：ON DELETE CASCADE

## 数据库的设计

   1. 多表之间的关系
       1. 分类：
           1. 一对一(了解)：
               * 如：人和身份证
               * 分析：一个人只有一个身份证，一个身份证只能对应一个人
           2. 一对多(多对一)：
               * 如：部门和员工
               * 分析：一个部门有多个员工，一个员工只能对应一个部门

3. 多对多：
* 如：学生和课程
* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

       2. 实现关系：
           1. 一对多(多对一)：
               * 如：部门和员工
               * 实现方式：在多的一方建立外键，指向一的一方的主键。

数据库05 约束外键多表
           2. 多对多：
               * 如：学生和课程
               * 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

数据库05 约束外键多表
           3. 一对一(了解)：
               * 如：人和身份证
               * 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

数据库05 约束外键多表

       3. 案例
           -- 创建旅游线路分类表 tab_category
           -- cid 旅游线路分类主键，自动增长
           -- cname 旅游线路分类名称非空，唯一，字符串 100
           CREATE TABLE tab_category (
               cid INT PRIMARY KEY AUTO_INCREMENT,
               cname VARCHAR(100) NOT NULL UNIQUE
           );

           -- 创建旅游线路表 tab_route
           /*
           rid 旅游线路主键，自动增长
           rname 旅游线路名称非空，唯一，字符串 100
           price 价格
           rdate 上架时间，日期类型
           cid 外键，所属分类
           */
           CREATE TABLE tab_route(
               rid INT PRIMARY KEY AUTO_INCREMENT,
               rname VARCHAR(100) NOT NULL UNIQUE,
               price DOUBLE,
               rdate DATE,
               cid INT,
               FOREIGN KEY (cid) REFERENCES tab_category(cid)
           );

           /*创建用户表 tab_user
           uid 用户主键，自增长
           username 用户名长度 100，唯一，非空
           password 密码长度 30，非空
           name 真实姓名长度 100
           birthday 生日
           sex 性别，定长字符串 1
           telephone 手机号，字符串 11
           email 邮箱，字符串长度 100
           */
           CREATE TABLE tab_user (
               uid INT PRIMARY KEY AUTO_INCREMENT,
               username VARCHAR(100) UNIQUE NOT NULL,
               PASSWORD VARCHAR(30) NOT NULL,
               NAME VARCHAR(100),
               birthday DATE,
               sex CHAR(1) DEFAULT '男',
               telephone VARCHAR(11),
               email VARCHAR(100)
           );

           /*
           创建收藏表 tab_favorite
           rid 旅游线路 id，外键
           date 收藏时间
           uid 用户 id，外键
           rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
           */
           CREATE TABLE tab_favorite (
               rid INT, -- 线路id
               DATE DATETIME,
               uid INT, -- 用户id
               -- 创建复合主键
               PRIMARY KEY(rid,uid), -- 联合主键
               FOREIGN KEY (rid) REFERENCES tab_route(rid),
               FOREIGN KEY(uid) REFERENCES tab_user(uid)
           );

数据库05 约束外键多表

https://blog.****.net/smallrain6/article/details/104722011

以上链接是数据库04的笔记

数据库05 约束 外键 多表

相关推荐

数据库05 约束外键多表