MySQL语句中的主键和外键怎么使用

MySQL语句中的主键和外键怎么使用

在关系型数据库中，主键（Primary Key）和外键（Foreign Key）是两个非常重要的概念。它们不仅用于确保数据的完整性和一致性，还在表与表之间建立关系。本文将详细介绍如何在MySQL中使用主键和外键，并通过示例代码帮助读者更好地理解这些概念。

1. 主键（Primary Key）

1.1 什么是主键？

主键是表中用于唯一标识每一行记录的列或列的组合。主键具有以下特性：

• 唯一性：主键的值必须是唯一的，不能有重复。
• 非空性：主键的值不能为NULL。
• 不可变性：主键的值一旦确定，通常不应被修改。

1.2 如何创建主键？

在MySQL中，可以在创建表时定义主键，也可以在表创建后通过ALTER TABLE语句添加主键。

1.2.1 创建表时定义主键

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT
);

在这个例子中，student_id列被定义为主键，并且使用了AUTO_INCREMENT属性，这意味着每次插入新记录时，student_id的值会自动递增。

1.2.2 创建表后添加主键

ALTER TABLE students
ADD PRIMARY KEY (student_id);

如果表已经存在，可以使用ALTER TABLE语句添加主键。

1.3 复合主键

有时，单个列不足以唯一标识一行记录，这时可以使用多个列的组合作为主键，称为复合主键。

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

在这个例子中，order_id和product_id的组合被定义为主键。

1.4 删除主键

如果需要删除主键，可以使用以下语句：

ALTER TABLE students
DROP PRIMARY KEY;

注意，删除主键后，表中的数据不会受到影响，但唯一性和非空性约束将被移除。

2. 外键（Foreign Key）

2.1 什么是外键？

外键是用于建立和加强两个表之间链接的列或列的组合。外键通常指向另一个表的主键。外键的主要作用是确保数据的引用完整性，防止插入无效数据。

2.2 如何创建外键？

在MySQL中，可以在创建表时定义外键，也可以在表创建后通过ALTER TABLE语句添加外键。

2.2.1 创建表时定义外键

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

在这个例子中，customer_id列被定义为外键，它引用了customers表中的customer_id列。

2.2.2 创建表后添加外键

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

如果表已经存在，可以使用ALTER TABLE语句添加外键。

2.3 外键约束

外键约束用于确保外键引用的数据必须存在于被引用的表中。MySQL支持以下几种外键约束：

• CASCADE：当被引用的表中的记录被删除或更新时，自动删除或更新引用表中的记录。
• SET NULL：当被引用的表中的记录被删除或更新时，将引用表中的外键列设置为NULL。
• RESTRICT：阻止删除或更新被引用的表中的记录，如果引用表中存在相关记录。
• NO ACTION：与RESTRICT类似，阻止删除或更新被引用的表中的记录。

2.3.1 使用CASCADE约束

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

在这个例子中，如果customers表中的某个customer_id被删除或更新，orders表中对应的记录也会被自动删除或更新。

2.3.2 使用SET NULL约束

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
);

在这个例子中，如果customers表中的某个customer_id被删除或更新，orders表中对应的customer_id列将被设置为NULL。

2.4 删除外键

如果需要删除外键，可以使用以下语句：

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

其中，fk_customer是外键的名称。如果外键没有指定名称，MySQL会自动生成一个名称，可以通过SHOW CREATE TABLE语句查看。

3. 主键和外键的使用场景

3.1 主键的使用场景

• 唯一标识记录：主键用于唯一标识表中的每一行记录，确保数据的唯一性。
• 提高查询性能：主键通常会自动创建索引，可以加快查询速度。
• 数据完整性：主键确保表中的每一行记录都有一个唯一的标识符，防止数据重复。

3.2 外键的使用场景

• 建立表与表之间的关系：外键用于建立两个表之间的关系，确保数据的引用完整性。
• 防止插入无效数据：外键约束确保插入的数据必须存在于被引用的表中，防止插入无效数据。
• 级联操作：外键约束可以自动处理被引用表中的数据更新或删除操作，确保数据的一致性。

4. 示例：使用主键和外键的完整案例

为了更好地理解主键和外键的使用，我们通过一个完整的案例来演示如何在MySQL中使用它们。

4.1 创建表

首先，我们创建两个表：customers和orders。

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

在这个例子中，customers表中的customer_id列被定义为主键，orders表中的customer_id列被定义为外键，并引用了customers表中的customer_id列。

4.2 插入数据

接下来，我们向customers表和orders表中插入一些数据。

INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

INSERT INTO orders (order_date, customer_id) VALUES
('2023-10-01', 1),
('2023-10-02', 2);

4.3 查询数据

我们可以通过以下查询语句查看插入的数据。

SELECT * FROM customers;
SELECT * FROM orders;

4.4 更新和删除数据

由于我们在orders表中定义了ON DELETE CASCADE和ON UPDATE CASCADE约束，当customers表中的记录被删除或更新时，orders表中的相关记录也会被自动处理。

4.4.1 更新customers表中的记录

UPDATE customers
SET customer_id = 3
WHERE customer_id = 1;

执行上述语句后，orders表中customer_id为1的记录会自动更新为3。

4.4.2 删除customers表中的记录

DELETE FROM customers
WHERE customer_id = 2;

执行上述语句后，orders表中customer_id为2的记录会自动删除。

5. 总结

主键和外键是MySQL中非常重要的概念，它们不仅用于确保数据的完整性和一致性，还在表与表之间建立关系。通过本文的介绍和示例，读者应该能够理解如何在MySQL中使用主键和外键，并在实际项目中应用这些知识。

在实际开发中，合理使用主键和外键可以大大提高数据库的性能和数据的一致性。希望本文能够帮助读者更好地掌握MySQL中的主键和外键的使用方法。