SQL FOREIGN KEY 约束

-- this table doesn't contain foreign keys
CREATE TABLE Customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
  );

-- create another table named Products
-- add foreign key to the customer_id column
-- the foreign key references the id column of the Customers table

CREATE TABLE Products (
    customer_id INTEGER ,
    name VARCHAR(100),
    FOREIGN KEY (customer_id)
    REFERENCES 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` 约束引用的表和列的名称

另请阅读：SQL CREATE TABLE

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

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

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

这意味着 customer_id（`Orders` 表）的值必须是 customer_id 列（`Customers` 表）中的值。

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

创建外键

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

-- this table doesn't have a 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)
);

-- add foreign key to the customer_id field
-- the foreign key references the id field of the Customers table
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 列。

注意：以上代码适用于所有主要的数据库系统。但是，根据数据库的不同，创建外键可能存在替代语法。请查阅其各自的数据库文档以获取更多信息。

在包含外键的表中插入记录

让我们尝试使用 SQL INSERT INTO 在包含外键的表中插入记录。

-- insert record into table with no foreign key first
INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- insert record into table with foreign key constraint in customer_id column
-- Insertion Success
INSERT INTO Orders
VALUES
(1, 'Keyboard', 400, 2),
(2, 'Mouse', 300, 2),
(3, 'Monitor', 12000, 1);

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

外键插入失败

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

-- insert record into table with no foreign key first
INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- insert record into table with a foreign key
-- insertion error because customer with id of 7 does not exist
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)
);

-- add foreign key to the customer_id field of Orders
-- the foreign key references the id field of Customers
ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(id);

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

一个表中的多个外键

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

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

-- this table doesn't have a foreign key
CREATE TABLE Users (
  id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10)
);

-- add foreign key to buyer and seller fields
-- foreign key references the id field of the Users table
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 PRIMARY KEY 约束