<think>我们正在讨论的是在一个表中同时为两个字段设置主键（复合主键）和外键（每个字段分别作为外键引用不同的表）的情况。
注意：复合主键由两个字段组成，而这两个字段又分别作为外键引用其他表的主键。
步骤：
1. 首先创建两个父表（被引用的表），每个父表都有一个主键。
2. 创建子表，在子表中定义两个字段，这两个字段将组成复合主键，并且每个字段分别作为外键引用两个不同的父表。
示例场景：
假设我们有两个父表：departments（部门表）和 employees（员工表）。
我们想要创建一个子表：department_managers（部门经理表），用于记录每个部门的经理是谁。
要求：
- 每个部门只能有一个经理（即部门编号唯一，但这里我们使用复合主键，实际上复合主键是部门编号和员工编号的组合，但这样设计是为了能够同时满足部门唯一和员工唯一吗？）
但是注意：我们通常不会这样设计，因为一个部门只能有一个经理，所以应该只用部门编号作为主键，然后经理员工编号作为外键引用员工表。然而，如果题目要求两个字段同时作为主键（复合主键）并且都是外键，我们可以设计如下：
重新设计场景：假设我们要记录每个部门中每个员工担任经理的时间（但一个部门可能有多个经理，比如不同时期），但这里我们为了满足复合主键，同时两个字段都是外键，可以这样：
父表1：departments（部门表）
dept_id INT PRIMARY KEY
父表2：employees（员工表）
emp_id INT PRIMARY KEY
子表：dept_managers（部门经理表）
dept_id INT,
emp_id INT,
start_date DATE,
end_date DATE,
PRIMARY KEY (dept_id, emp_id), -- 复合主键
FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
但是注意：这样设计意味着同一个部门可以有多名员工担任经理（只要员工不同），但同一名员工也可以在多个部门担任经理（只要部门不同）。但复合主键保证了同一个部门同一个员工只能有一条记录。
然而，如果业务要求一个部门同时只能有一个经理（即部门唯一），那么我们就不能将dept_id和emp_id一起作为复合主键，因为这样同一个部门可以插入多个不同的员工。所以在这种情况下，我们应该将dept_id设为主键（单字段），同时emp_id作为外键。但题目要求两个字段都设置为主键（复合主键）并且分别作为外键，所以我们按照复合主键设计，但业务含义就变成了一个部门可以有多个经理（每个经理一个员工，且一个员工可以在多个部门当经理），这实际上是一种多对多的关系，但用复合主键来避免重复。
如果业务要求一个部门只能有一个经理（一对一），那么我们就不能用复合主键，而应该用dept_id作为主键（同时也是外键）引用部门表，并且emp_id作为外键引用员工表，同时emp_id还要设置唯一约束（因为一个员工只能管理一个部门）。但是题目要求是两个字段同时作为主键（复合主键）并且都是外键。
因此，我们按照复合主键的设计，并建立两个外键约束。
代码示例：
创建父表：
```sql
-- 创建部门表
CREATE TABLE departments (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE
);
-- 创建员工表
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL
);
```
创建子表（复合主键，两个外键）：
```sql
CREATE TABLE dept_managers (
dept_id INT,
emp_id INT,
-- 其他字段，比如开始日期等
start_date DATE,
-- 设置复合主键
PRIMARY KEY (dept_id, emp_id),
-- 设置外键约束：dept_id 引用 departments 表的 dept_id
CONSTRAINT fk_dept_mgr_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE,
-- 设置外键约束：emp_id 引用 employees 表的 emp_id
CONSTRAINT fk_dept_mgr_emp
FOREIGN KEY (emp_id)
REFERENCES employees(emp_id)
ON DELETE CASCADE
);
```
注意：
1. 复合主键由两个字段组成，因此每个组合(dept_id, emp_id)必须是唯一的。
2. 两个外键分别引用两个不同的表。
3. 外键约束命名（fk_dept_mgr_dept 和 fk_dept_mgr_emp）是可选的，但建议命名以便后续管理。
插入数据示例：
```sql
-- 插入部门数据
INSERT INTO departments (dept_name) VALUES ('研发部'), ('销售部');
-- 插入员工数据
INSERT INTO employees (emp_name) VALUES ('张三'), ('李四');
-- 插入部门经理数据
INSERT INTO dept_managers (dept_id, emp_id, start_date)
VALUES (1, 1, '2023-01-01'); -- 部门1的经理是员工1
-- 同一个部门，不同的员工（允许，因为复合主键不同）
INSERT INTO dept_managers (dept_id, emp_id, start_date)
VALUES (1, 2, '2023-01-01'); -- 部门1的另一个经理？这取决于业务，但复合主键允许
-- 同一个员工，不同的部门（允许）
INSERT INTO dept_managers (dept_id, emp_id, start_date)
VALUES (2, 1, '2023-01-01'); -- 部门2的经理是员工1
-- 重复插入相同的部门相同的员工（失败，因为违反主键唯一性）
INSERT INTO dept_managers (dept_id, emp_id, start_date)
VALUES (1, 1, '2023-02-01'); -- 错误：主键重复
```
如果业务要求一个部门只能有一个经理（即dept_id必须唯一），那么我们需要在dept_id上添加唯一约束：
```sql
CREATE TABLE dept_managers_unique_dept (
dept_id INT UNIQUE, -- 添加唯一约束，确保一个部门只能出现一次
emp_id INT,
start_date DATE,
PRIMARY KEY (dept_id, emp_id), -- 复合主键
FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE
);
```
但是注意：复合主键已经包含了dept_id，而唯一约束要求dept_id唯一，那么复合主键中的emp_id就没有意义了（因为dept_id唯一，那么(dept_id, emp_id)实际上就是dept_id唯一，emp_id只能是固定的一个）。所以这种情况下，我们通常不会使用复合主键，而是使用dept_id作为主键（单列主键），然后emp_id作为外键并加上唯一约束。
但题目要求是两个字段同时作为主键（复合主键），所以我们按照复合主键设计，并额外给dept_id添加唯一约束以满足业务（一个部门只能有一个经理）。这样设计后，复合主键实际上可以由(dept_id, emp_id)组成，但由于dept_id唯一，所以emp_id也就被唯一确定了（一个部门只有一个经理，但一个经理可以管理多个部门？不，因为dept_id唯一，所以一个经理只能出现在一个部门中？不对，因为唯一约束只保证dept_id不重复，但同一个经理可以出现在不同的dept_id行中（即管理多个部门））。所以这仍然不符合一个部门只能有一个经理，一个经理只能管理一个部门（一对一）的情况。
为了同时满足：
- 一个部门只能有一个经理（dept_id唯一）
- 一个经理只能管理一个部门（emp_id唯一）
我们需要在dept_id和emp_id上分别建立唯一约束。这样，复合主键可以保证组合唯一，而两个单独的唯一约束则分别保证了部门唯一和经理唯一。
修改后的建表语句：
```sql
CREATE TABLE dept_managers_one_to_one (
dept_id INT UNIQUE, -- 确保一个部门只有一个经理
emp_id INT UNIQUE, -- 确保一个经理只管理一个部门
start_date DATE,
PRIMARY KEY (dept_id, emp_id), -- 复合主键（实际上这两个唯一约束已经保证了复合主键的唯一性，但主键定义仍然需要）
FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE
);
```
注意：由于在dept_id和emp_id上分别添加了唯一约束，这张表就实现了一对一的关系。
总结：
根据业务需求，我们可以选择是否添加额外的唯一约束。题目要求是同时设置两个字段为主键（复合主键）并且都是外键，我们给出了基础版本和添加唯一约束的版本。
因此，回答用户的问题：同时给一个表中两个字段设置主键（复合主键）和外键（分别引用不同表）的代码就是上面的示例。
注意：如果两个外键引用的是同一个表（自引用），写法类似，只需将两个外键都指向同一个表的主键即可。
示例：员工表（employees）中的经理字段（假设每个员工有一个直接上级，但直接上级也是员工）并且要求员工编号和经理编号组成复合主键？这通常不合理，因为复合主键要求唯一，而员工编号本身就是唯一的，所以复合主键没有必要。但如果是其他场景，比如记录员工之间的协作关系（多对多），则可以设计中间表，其中两个字段都是外键引用员工表，并且这两个字段组成复合主键。
例如：员工协作表
```sql
CREATE TABLE employee_collaboration (
emp_id1 INT,
emp_id2 INT,
project_id INT,
PRIMARY KEY (emp_id1, emp_id2), -- 复合主键
FOREIGN KEY (emp_id1) REFERENCES employees(emp_id),
FOREIGN KEY (emp_id2) REFERENCES employees(emp_id)
);
```
因此，具体设计取决于业务需求。
下面给出用户所需的代码（基础版本，两个字段分别为外键，且这两个字段组成复合主键）：
</think>以下是在MySQL中同时为表中两个字段设置主键和外键的完整代码示例及详细说明：
### 场景说明
假设我们需要建立包含以下关系的数据库模型：
- `departments` 表：存储部门信息
- `employees` 表：存储员工信息
- `department_managers` 表：记录部门经理（需要同时设置复合主键和外键）
### 完整代码实现
```sql
-- 创建父表1：部门表
CREATE TABLE departments (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;
-- 创建父表2：员工表
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;
-- 创建子表：部门经理表（包含复合主键和外键）
CREATE TABLE department_managers (
dept_id INT NOT NULL,
manager_id INT NOT NULL,
assignment_date DATE NOT NULL,
-- 定义复合主键（两个字段组成）
PRIMARY KEY (dept_id, manager_id),
-- 定义外键1：引用部门表
CONSTRAINT fk_manager_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE,
-- 定义外键2：引用员工表
CONSTRAINT fk_manager_emp
FOREIGN KEY (manager_id)
REFERENCES employees(emp_id)
ON DELETE RESTRICT
) ENGINE=InnoDB;
```
### 关键点解析
1. **复合主键设置**
```sql
PRIMARY KEY (dept_id, manager_id)
```
- 同时指定`dept_id`和`manager_id`作为主键
- 保证每个部门-经理组合的唯一性（相同部门不能有重复经理，同一经理不能管理同一部门多次）[^1]
2. **双外键设置**
```sql
-- 部门外键
CONSTRAINT fk_manager_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE,
-- 经理外键
CONSTRAINT fk_manager_emp
FOREIGN KEY (manager_id)
REFERENCES employees(emp_id)
ON DELETE RESTRICT
```
- `dept_id` 引用 `departments.dept_id`
- `manager_id` 引用 `employees.emp_id`
- 使用不同的删除策略：
- `CASCADE`：删除部门时自动删除关联记录
- `RESTRICT`：阻止删除有管理关系的员工[^3]
3. **约束命名**
- `fk_manager_dept` 和 `fk_manager_emp` 为显式约束命名
- 优点：便于后续管理（修改/删除约束）
### 数据操作示例
```sql
-- 插入基础数据
INSERT INTO departments (dept_name) VALUES ('研发部'), ('市场部');
INSERT INTO employees (emp_name) VALUES ('张三'), ('李四');
-- 成功插入（有效的外键引用）
INSERT INTO department_managers (dept_id, manager_id, assignment_date)
VALUES (1, 1, '2023-01-01'); -- 研发部张三
-- 失败插入（违反外键约束）
INSERT INTO department_managers (dept_id, manager_id, assignment_date)
VALUES (99, 1, '2023-01-01'); -- 错误：无效的部门ID
-- 失败插入（违反主键唯一性）
INSERT INTO department_managers (dept_id, manager_id, assignment_date)
VALUES (1, 1, '2023-02-01'); -- 错误：重复的主键
```
### 性能优化建议
1. **批量导入数据时**
```sql
-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
-- 执行批量导入操作
LOAD DATA INFILE 'data.csv' INTO TABLE department_managers;
-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;[^2]
```
2. **索引优化**
- 复合主键自动创建聚簇索引
- 外键自动创建二级索引
- 确保关联查询性能最佳
### 设计注意事项
1. **适用场景**
- 多对多关系（如学生选课、产品分类）
- 需要强约束的关联关系
- 历史记录跟踪（如部门经理变更）
2. **数据一致性**
- 插入前确保父表存在对应记录
- 使用事务保证多表操作的原子性
- 合理选择`ON DELETE`策略维护数据完整[^3]
---
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
