SQL FOREIGN KEY约束

提示

1.外键的定义和作用：在SQL中，FOREIGN KEY约束用于在两个表之间建立关系，确保一个表中的字段值引用另一个表中相应字段的值。

2.外键的语法和创建方法：FOREIGN KEY约束通过指定FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name)来创建。可以在创建表时直接定义，也可通过ALTER TABLE命令添加到现有表。

3.外键的使用场景和好处：外键有助于数据规范化、避免错误数据插入，保证数据的完整性和一致性。它允许在表间建立连接，使得每个表能够维护其数据的特殊部分，同时保持相互关系。

在 SQL 中，FOREIGN KEY 约束用于在两个表之间创建关系。外键是使用 FOREIGN KEY 和 REFERENCES 关键字定义的。

示例

CREATE TABLE Customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
  );

-- 创建另一个名为 Products 的表
-- 在 customer_id 列中添加外键
-- 外键引用 Customers 表的 id 列

CREATE TABLE Products (
    customer_id INTEGER,
    name VARCHAR(100),
    FOREIGN KEY (customer_id)
    REFERENCES Customers(id)
  );

这里，Products 表中的 customer_id 列引用了 Customers 表中的 id 列。

SQL 中的外键语法

SQL FOREIGN KEY 约束的语法是：

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    FOREIGN KEY (column_name)
    REFERENCES referenced_table_name (referenced_column_name)
);

这里，

• table_name 是定义 FOREIGN KEY 约束的表的名称
• column_name 是定义 FOREIGN KEY 约束的列的名称
• referenced_table_name 和 referenced_column_name 是 FOREIGN KEY 约束引用的表和列的名称

使用 FOREIGN KEY 引用另一个表中的列

SQL 中的 FOREIGN KEY 约束通过将一个表中的列链接到另一个表中的列，建立两个表之间的关系。例如，

这里，Orders 表中的 customer_id 字段是一个引用 Customers 表中 customer_id 字段的 FOREIGN KEY。

这意味着 Orders 表中的 customer_id 的值必须来自 Customers 表中 customer_id 列的值。

注意： 外键可以引用父表中的任何列。然而，通常的做法是将外键引用父表的主键。

创建外键

现在，让我们看看如何在数据库中创建外键约束。

CREATE TABLE Customers (
  id INT,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10),
  CONSTRAINT CustomersPK PRIMARY KEY (id)
);

-- 在 customer_id 字段中添加外键
-- 外键引用 Customers 表的 id 字段
CREATE TABLE Orders (
  order_id INT,
  product VARCHAR(40),
  total INT,
  customer_id INT,
  CONSTRAINT OrdersPK PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

在 Orders 表中的 customer_id 列的值引用了名为 Customers 的另一个表中的 id 列的行。

注意： 上述代码适用于所有主要的数据库系统。然而，根据数据库的不同，创建外键的语法可能有所不同。有关更多信息，请参阅各自的数据库文档。

在带有外键的表中插入记录

让我们尝试在带有外键的表中插入记录。

INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- 然后向有外键约束的 customer_id 列的表中插入记录
-- 插入成功
INSERT INTO Orders
VALUES
(1, 'Keyboard', 400, 2),
(2, 'Mouse', 300, 2),
(3, 'Monitor', 12000, 1);

这里，查询成功执行，因为我们尝试插入到 Orders 表中的行在 customer_id 列中有有效值，该列在 Customers 表中有一个 FOREIGN KEY 约束。

外键插入失败

当向一个表的外键列输入一个与相关表的主键列中的任何值都不匹配的值时，会发生插入失败。例如，

INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- 然后向有外键的表中插入记录
-- 插入错误，因为 id 为 7 的客户不存在
INSERT INTO Orders
VALUES (4, 'Keyboard', 400, 7);

这里，向 Orders 表中插入行失败，因为 7 不是 Customers 表中有效的 customer_id 值。因此，这个查询违反了 FOREIGN KEY 约束。

为什么使用外键？

外键是关系型数据库的重要部分，我们使用它们的原因如下：

规范化数据

FOREIGN KEY 约束有助于我们在多个表中规范化数据并减少冗余。这意味着一个数据库可以有多个彼此相关的表。

防止错误数据插入

如果两个数据库表通过一个字段（属性）相关联，使用 FOREIGN KEY 可以确保该字段中不插入错误数据。这有助于在数据库层面消除错误。

推荐阅读： SQL JOIN

使用 ALTER TABLE 添加外键

可以使用 ALTER TABLE 命令向现有表添加 FOREIGN KEY 约束。例如，

CREATE TABLE Customers (
  id INT,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10),
  CONSTRAINT CustomersPK PRIMARY KEY (id)
);

CREATE TABLE Orders (
  order_id INT,
  item VARCHAR(40),
  amount INT,
  customer_id INT,
  CONSTRAINT OrdersPK PRIMARY KEY (order_id)
);

-- 向 Orders 表的 customer_id 字段添加外键
-- 外键引用 Customers 表的 id 字段
ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(id);

注意： 此操作不受我们基于 SQLite 的在线 SQL 编辑器的支持。

表中的多个外键

数据库表也可以有多个外键。

例如，假设我们需要记录所有交易，其中每个用户既是买家又是卖家。

CREATE TABLE Users (
  id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10)
);

-- 在买家和卖家字段中添加外键
-- 外键引用 Users 表的 id 字段
CREATE TABLE Transactions (
  transaction_id INT PRIMARY KEY,
  amount INT,
  seller INT,
  buyer INT,
  CONSTRAINT fk_seller FOREIGN KEY (seller) REFERENCES Users(id),
  CONSTRAINT fk_buyer FOREIGN KEY (buyer) REFERENCES Users(id)
);

这里，SQL 命令在 Transactions 表中创建了两个外键（buyer 和 seller）。

注意： 与其他约束一样，使用 CONSTRAINT constraint_name 命名 FOREIGN KEY 约束是可选的。但这样做使得更改或删除约束更容易。当定义多个约束时，这尤其有帮助。

推荐阅读：

• SQL 约束
• SQL 主键约束