MySQL主键和外键详解及操作步骤

一、主键（Primary Key）

1. 什么是主键

主键是数据库表中用于唯一标识每一行记录的字段或字段组合。它具有以下特性：

• 唯一性：主键值在整个表中必须是唯一的，不能有重复。

• 非空性：主键字段的值不能为NULL。

• 一个表只能有一个主键：但主键可以由多个字段组成（复合主键）。

2. 主键的作用

• 唯一标识记录：确保每条记录在表中都有唯一的标识符。

• 提高查询性能：MySQL会自动为主键创建索引，加快数据检索速度。

• 建立表间关系：作为外键引用的目标，用于构建表之间的关联关系。

3. 创建主键的方法

方法一：创建表时定义主键

-- 单列主键
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);

-- 复合主键
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

方法二：修改现有表添加主键

-- 假设表已存在，且id字段无重复值和NULL值
ALTER TABLE students ADD PRIMARY KEY (id);

4. 查看主键

-- 查看表结构，主键会显示在Key列
DESCRIBE students;

-- 或者使用SHOW CREATE TABLE查看更详细的信息
SHOW CREATE TABLE students;

5. 删除主键

ALTER TABLE students DROP PRIMARY KEY;

注意：如果主键是自增字段，需要先移除自增属性才能删除主键。

6. 主键设计原则

• 选择合适的数据类型：通常使用INT、BIGINT等整数类型，性能好且占用空间小。

• 避免使用业务字段：尽量不要使用具有业务含义的字段（如身份证号、手机号）作为主键，因为业务规则可能会变化。

• 考虑使用自增主键：对于大多数场景，自增INT或BIGINT是简单高效的选择。

• 分布式系统考虑UUID：在分布式系统中，可能需要使用UUID等非自增主键来避免冲突。

二、外键（Foreign Key）

1. 什么是外键

外键是表中的字段或字段组合，它引用另一个表的主键。外键用于建立和维护两个表之间的关联关系。

2. 外键的作用

• 保证数据完整性：确保从表中的数据必须在主表中存在对应的记录。

• 建立表间关系：明确表与表之间的关联，如一对多、多对多关系。

• 级联操作：可以设置级联更新、级联删除等操作，保持数据一致性。

3. 创建外键的方法

方法一：创建表时定义外键

-- 先创建主表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 再创建从表并定义外键
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

方法二：修改现有表添加外键

ALTER TABLE employees 
ADD CONSTRAINT fk_dept 
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

其中fk_dept是外键约束的名称，建议命名规范为fk_从表名_主表名。

4. 外键约束的行为

创建外键时可以指定当主表记录被更新或删除时的行为：

• RESTRICT（默认）：拒绝删除或更新主表记录，如果从表中有相关引用。

• CASCADE：级联操作，主表记录删除或更新时，从表中相关记录也会被删除或更新。

• SET NULL：主表记录删除或更新时，从表中相关字段设为NULL（前提是从表字段允许NULL）。

• NO ACTION：与RESTRICT类似，不同数据库实现可能有差异。

• SET DEFAULT：主表记录删除或更新时，从表中相关字段设为默认值。

示例：带级联操作的外键

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

上述示例中，当departments表中的dept_id更新时，employees表中对应的dept_id也会更新；当departments表中的记录被删除时，employees表中相关的记录也会被删除。

5. 查看外键

-- 查看表的外键约束
SELECT 
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name' 
AND TABLE_NAME = 'employees'
AND REFERENCED_TABLE_NAME IS NOT NULL;

将your_database_name替换为实际的数据库名。

6. 删除外键

ALTER TABLE employees DROP FOREIGN KEY fk_dept;

其中fk_dept是要删除的外键约束名称。

7. 外键使用注意事项

• 存储引擎支持：InnoDB存储引擎支持外键，MyISAM不支持。

• 数据类型匹配：外键字段和引用字段的数据类型必须一致。

• 索引要求：外键字段通常需要有索引以提高性能。

• 性能影响：大量使用外键可能会影响插入、更新和删除操作的性能。

• 循环引用：避免表之间的循环外键引用，这会导致操作困难。

三、主键与外键的关系

主键和外键是数据库中建立表间关系的核心机制：

• 主键唯一标识表中的记录，外键引用其他表的主键。

• 通过外键可以实现数据的参照完整性，确保相关数据的一致性。

• 常见的关系类型：

• 一对一：从表的外键是主表的主键，且从表外键字段设为UNIQUE。

• 一对多：从表的外键引用主表的主键，是最常见的外键应用场景。

• 多对多：通过中间表的两个外键分别引用两个主表的主键来实现。

多对多关系示例

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);

-- 中间表，实现多对多关系
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

四、总结

主键和外键是MySQL数据库中非常重要的概念：

• 主键用于唯一标识表中的记录，保证数据的唯一性和非空性。

• 外键用于建立表间的关联关系，保证数据的参照完整性。

• 合理使用主键和外键可以提高数据库的性能和数据质量。

• 在设计数据库时，需要根据实际需求选择合适的字段作为主键，并建立必要的外键约束。

掌握主键和外键的使用方法，对于设计合理、高效的数据库结构至关重要。

MySQL主键 外键约束 数据库表关系 主键创建 外键操作