MySQL 外键实战练习：操作关联表

(1) 启动MySQL服务器。

启动方法视不同操作系统而定，此处不再赘述。

(2) 登录MySQL服务器。

在命令行或图形界面工具中输入合适的用户名和密码登录MySQL服务器。

(3) 创建数据库exp07。

CREATE DATABASE exp07;

(4) 切换当前数据库。

USE exp07;

(5) 按要求创建以上三张表格（先不要插入数据）。

CREATE TABLE customer (
  c_id CHAR(6) PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  location VARCHAR(30),
  salary DECIMAL(8,2)
);

CREATE TABLE bank (
  b_id CHAR(6) PRIMARY KEY,
  bank_name VARCHAR(30) NOT NULL
);

CREATE TABLE deposite (
  d_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  c_id CHAR(6),
  b_id CHAR(6),
  dep_date DATE,
  amount DECIMAL(8,2),
  CONSTRAINT fk_customer FOREIGN KEY (c_id) REFERENCES customer(c_id),
  CONSTRAINT fk_bank FOREIGN KEY (b_id) REFERENCES bank(b_id)
);

(6) 以默认模式建立外键。查看建表语句。

在上一步已经创建了带有外键约束的deposite表，可以使用以下命令查看建表语句：

SHOW CREATE TABLE deposite;

输出结果如下：

CREATE TABLE `deposite` (
  `d_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` char(6) DEFAULT NULL,
  `b_id` char(6) DEFAULT NULL,
  `dep_date` date DEFAULT NULL,
  `amount` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`d_id`),
  KEY `fk_customer` (`c_id`),
  KEY `fk_bank` (`b_id`),
  CONSTRAINT `deposite_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customer` (`c_id`),
  CONSTRAINT `deposite_ibfk_2` FOREIGN KEY (`b_id`) REFERENCES `bank` (`b_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

可以看到，外键约束已经被正确创建。

(7) 请首先插入deposite表中数据，是否能够执行？若不能执行请解释其原因。

不能执行，因为表中的外键约束要求c_id和b_id在对应的主表customer和bank中存在，而这两张表目前还没有插入数据。

(8) 请插入customer表数据，是否能够执行？若不能执行请解释其原因。

可以执行，因为customer表没有外键约束。

INSERT INTO customer (c_id, name, location, salary) VALUES 
('101001', '孙萌', '广州', 1234),
('101002', '王琦', '南京', 3526),
('101003', '赵越', '北京', 6892),
('101004', '童彤', '海南', 3492);

(9) 请再次插入deposite表中数据，是否能够执行？若不能执行请解释其原因。

不能执行，因为表中的外键约束要求c_id和b_id在对应的主表customer和bank中存在，而bank表目前还没有插入数据。

(10) 请将各表格中数据添加完成，记录执行过程中表的先后顺序。

先插入customer表，再插入bank表，最后插入deposite表。

INSERT INTO bank (b_id, bank_name) VALUES 
('B0001', '工商银行'),
('B0002', '建设银行'),
('B0003', '中国银行'),
('B0004', '农业银行');

INSERT INTO deposite (c_id, b_id, dep_date, amount) VALUES 
('101001', 'B0001', '2011-04-05', 42526),
('101002', 'B0003', '2012-12-02', 66500),
('101003', 'B0002', '2018-11-21', 500000),
('101004', 'B0004', '2020-03-02', 12987),
('101001', 'B0002', '2015-09-02', 456),
('101002', 'B0004', '2018-09-02', 99000),
('101003', 'B0003', '2016-06-22', 35000),
('101004', 'B0001', '2016-03-12', 67890),
('101001', 'B0004', '2019-11-02', 400000),
('101002', 'B0001', '2017-08-22', 12000),
('101003', 'B0002', '2015-09-02', 12345.98),
('101004', 'B0003', '2014-10-13', 5678.88);

(11) 在customer表中删除孙萌的数据记录，是否能够执行？若不能执行请解释其原因。

不能执行，因为deposite表中有外键约束要求c_id在customer表中存在。

(12) 在bank表中删除中国银行的数据记录，是否能够执行？若不能执行请解释其原因。

不能执行，因为deposite表中有外键约束要求b_id在bank表中存在。

(13) 在deposite表中删除d_id分别为2、7、12的数据记录，是否能够执行？若不能执行请解释其原因。

可以执行，因为没有涉及到外键约束。

DELETE FROM deposite WHERE d_id IN (2, 7, 12);

(14) 在bank表中删除中国银行的数据记录，是否能够执行？若不能执行请解释其原因。

不能执行，因为deposite表中有外键约束要求b_id在bank表中存在。

(15) 在deposite表中删除d_id分别为1、5、9的数据记录，后在custome表中删除孙萌的数据记录，是否能够执行？若不能执行请解释其原因。

不能执行，因为deposite表中有外键约束要求c_id和b_id在customer表和bank表中存在，而删除孙萌的数据会违反deposite表中的外键约束。

(16) 在deposite表中将d_id=3的b_id更新为B0003，是否能够执行？若不能执行请解释其原因。

可以执行，因为更新后仍然符合deposite表中的外键约束。

UPDATE deposite SET b_id='B0003' WHERE d_id=3;

(17) 请思考，如希望有外键关联的主表删除数据不会报错，可以如何设置？

可以设置外键约束时，使用ON DELETE CASCADE选项，表示当主表中有数据被删除时，从表中的对应数据会被自动删除。

例如，在创建deposite表时，可以使用以下语句创建外键约束：

CREATE TABLE deposite (
  d_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  c_id CHAR(6),
  b_id CHAR(6),
  dep_date DATE,
  amount DECIMAL(8,2),
  CONSTRAINT fk_customer FOREIGN KEY (c_id) REFERENCES customer(c_id) ON DELETE CASCADE,
  CONSTRAINT fk_bank FOREIGN KEY (b_id) REFERENCES bank(b_id) ON DELETE CASCADE
);

这样在删除customer或bank表中的数据时，对应的deposite表中的数据也会被自动删除。