sql server 外键_什么是SQL Server中的外键

sql server 外键

In this article, we will seek an answer to an important question – “What is a foreign key in SQL Server?”. At the same time, we will give some seconder answers to this question. In this way, we can understand the foreign key concept more clearly.

在本文中，我们将寻求一个重要问题的答案-“ SQL Server中的外键是什么？”。 同时，我们将对此问题给出一些补充答案。 这样，我们可以更清楚地了解外键概念。

介绍 (Introduction)

A foreign key is a column or set of columns that allow us to establish a referential link between the data in two tables. This referential link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

外键是允许我们在两个表中的数据之间建立引用链接的一列或一组列。 该引用链接有助于将外键列数据与引用表数据的数据进行匹配。 被引用的表称为父表，而涉及外键的表称为子表。 此外，如果外键引用同一表的另一列，则此引用类型称为自引用。

Until this part of the article, we have answered the “What is a foreign key in SQL” question briefly. Now, we will take some examples in order to understand the foreign key designing model and usage details.

在本文的这一部分之前，我们已经简要回答了“ SQL中的外键是什么”问题。 现在，我们将通过一些示例来了解外键设计模型和使用细节。

创建外键 (Create a foreign key )

Suppose that we have two tables and the first one is the Customers table which stores detailed information about the customers of an organization. The other one is CustomerOrders that stores the order details of the clients. According to the database design, the CustomerOrders table must not contain any invalid customer data. To overcome this issue, we need to create a foreign key between Customers and CustomerOrders columns. The following illustration shows the design of these two tables:

假设我们有两个表，第一个是“ 客户”表，该表存储有关组织的客户的详细信息。 另一个是CustomerOrders ，它存储客户的订单详细信息。 根据数据库设计， CustomerOrders表 不得包含任何无效的客户数据。 为了解决此问题，我们需要在“ 客户”和“ 客户订单”列之间创建一个外键。 下图显示了这两个表的设计：

This foreign key establishes referential integrity between Customers and CustomerOrders tables, thus, restricting the insertion of a new row when the CustomerId value of the inserted row does not match the ID column values of the Customers table.

此外键在Customer表和CustomerOrders表之间建立了引用完整性，因此，当插入的行的CustomerId值与Customer表的ID列值不匹配时，将限制新行的插入。

For this example, the Customers table is the parent table and the CustomerOrders table is the child table.

在此示例中， Customers表是父表，而CustomerOrders表是子表。

• Tip: What is a foreign key in SQL Server: It creates a link between parent and child table columns and foreign key references a primary key in the parent table.

  提示： 什么是SQL Server中的外键：它在父表和子表列之间创建链接，并且外键引用父表中的主键。

At first, we will create a Customers table through the following query and we will populate some sample data:

首先，我们将通过以下查询创建一个Customer表，并填充一些示例数据：

CREATE TABLE Customers
(ID INT PRIMARY KEY , 
 CustomerName VARCHAR(50), 
 CustomerAge  SMALLINT, 
 CustomerCountry  VARCHAR(50)
)
 INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (1,N'Salvador',23,N'Brazil ')
INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (2,N'Lawrence',60,N'China ')
INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (3,N'Ernest',38,N'India')

The following query will create the CustomerOrders table and the CustomerId column will be foreign key and it references the ID column of the Customers table. SQL Server automatically gives a name to the foreign key that will be created.

以下查询将创建CustomerOrders表，并且CustomerId列将是外键，并且它将引用Customer表的ID列。 SQL Server自动为将创建的外键命名。

CREATE TABLE CustomerOrders
(ID INT PRIMARY KEY ,
 OrderDate DATETIME, 
 CustomerID INT FOREIGN KEY REFERENCES Customers(ID), 
 Amout   BIGINT, 
)

When we want to insert a new row into the CustomerOrders table, the value of the CustomerID must match the values in the ID columns of the Customers table.

当我们要插入新行到CustomerOrders表， 客户id的值必须Customers表的ID列中的值相匹配。

INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (1,CAST('29-Apr-2019' AS DATETIME),1,968.45)
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (2,CAST('10-May-2019' AS DATETIME),2,898.36)
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (3,CAST('21-Oct-2019' AS DATETIME),3,47.01)
 
SELECT * FROM [CustomerOrders]

The previous insert batch statement does not return any error because all CustomerID values match ID column values in the Customers tables.

前面的插入批处理语句不返回任何错误，因为所有CustomerID值都与Customer表中的ID列值匹配。

The following query will return an error and the insert statement will be rolled back because the Customers table does not contain any row that has an ID value equal “4”:

以下查询将返回错误，并且insert语句将被回退，因为Customers表不包含ID值等于“ 4”的任何行：

INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) 
VALUES (4,CAST('29-Apr-2019' AS DATETIME),4,968.45)

If we want to give a name explicitly to the foreign key constraint, we can use the following query:

如果我们想为外键约束明确命名，可以使用以下查询：

CREATE TABLE CustomerOrders
(ID INT PRIMARY KEY ,
 OrderDate DATETIME, 
 CustomerID INT ,
 Amout   BIGINT,
 CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID)  REFERENCES customers(ID)
)

• Tip: What is a foreign key in SQL Server: It is a constraint that provides referential integrity between two tables.

  提示： 什么是SQL Server中的外键：这是一个约束，可在两个表之间提供引用完整性。

After creating a table, we can add a foreign key to this table. We can use the following query in order to add a foreign key to the existing table:

创建表后，我们可以向该表添加外键。 我们可以使用以下查询来向现有表添加外键：

CREATE TABLE CustomerOrders
(ID INT PRIMARY KEY,
 OrderDate DATETIME, 
 CustomerID INT,
 Amout   BIGINT
)
 
ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID)  REFERENCES Customers(ID)

Tip: The following query will help to find out more details about foreign key relations in the executed database.

提示： 以下查询将有助于在执行的数据库中查找有关外键关系的更多详细信息。

SELECT OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table', 
       OBJECT_NAME(FK.parent_object_id) AS 'Referring Table', 
       FK.name AS 'Foreign Key', 
       COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) AS 'Referenced Column', 
       COL_NAME(FK.parent_object_id, FKC.parent_column_id) AS 'Referring Column'
FROM sys.foreign_keys AS FK
     INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID

外键更新和删除规则 (Foreign key update and delete rules)

As we mentioned, the main purpose of the foreign key is to provide the referential integrity between parent and child table. Sometimes, we may need to update or delete data from the parent table. In this case, we have to decide the behavior of the child table data because it is referenced to the parent table. In the SQL Server, we can specify delete and update rules for the foreign keys so we can determine the behavior of the child data when we want to update or delete some data from the parent table.

如前所述，外键的主要目的是在父表和子表之间提供引用完整性。 有时，我们可能需要更新或删除父表中的数据。 在这种情况下，我们必须决定子表数据的行为，因为它是引用到父表的。 在SQL Server中，我们可以为外键指定删除和更新规则，以便在要更新或删除父表中的某些数据时确定子数据的行为。

• Tip: What is a foreign key in SQL Server: The primary purpose of the foreign key is to establish control upon the data that will be inserted into the table, which involves foreign key. The inserted data must match the referenced table.

  提示： 什么是SQL Server中的外键：外键的主要目的是建立对将插入表的数据的控制，其中涉及外键。 插入的数据必须与引用的表匹配。

These rules are:

这些规则是：

删除规则： (Delete Rules:)

• No Action: It returns an error when we want to delete any row from the parent table and the deleted statement will be rolled back 无操作：当我们要从父表中删除任何行并且被删除的语句将回滚时，它将返回错误
• Cascade: In this option, the deleted statement also deletes all associated rows from the child table Cascade ：在此选项中，delete语句还从子表中删除所有关联的行
• Set Null: In this option, the deleted statement deletes the parent table row and associated values will be updated with null values on the child table. The foreign key column must be nullable Set Null ：在此选项中，delete语句删除父表行，并且关联值将用子表上的空值更新。 外键列必须可以为空
• Set Default: The delete statement deletes parent table row and associated values of the child table will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table 设置默认值 ：delete语句删除父表行，并将使用外键列的默认值更新子表的关联值。 为了执行此规则，应为外键列指定默认约束，并且该默认值必须在父表中匹配

更新规则： (Update Rules:)

• No Action: It returns an error when we want to update any row from the parent table and the update statement will be rolled back 无操作 ：当我们要更新父表中的任何行并且更新语句将回滚时，它将返回错误
• Cascade: In this option, the updated statement also updates all associated rows from the child table Cascade ：在此选项中，更新后的语句还会更新子表中的所有关联行
• Set Null: In this option, the updated statement updates the parent table data and the associated values of the child table are updated to a default value. The foreign key column must be nullable Set Null ：在此选项中，更新后的语句更新父表数据，并将子表的关联值更新为默认值。 外键列必须可以为空
• Set Default: The update statement updates the parent table row and child table associated values will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table 设置默认值 ：update语句更新父表行，并且与子表关联的值将使用外键列的默认值进行更新。 为了执行此规则，应为外键列指定默认约束，并且该默认值必须在父表中匹配

In the following example, we will create a CustomerSales table and this table CustomerId column referencing ID column of the Customers table. The delete rule of the foreign key will be specified as No Action and the update rule will be specified as Set Null option.

在下面的示例中，我们将创建一个CustomerSales表，并且此表的CustomerId列将引用Customer表的ID列。 外键的删除规则将指定为“ 无操作” ，更新规则将指定为“ 设置为空”选项。

CREATE TABLE CustomerSales
(ID  INT
 PRIMARY KEY,
 SaleDate DATETIME, 
 CustomerID INT FOREIGN KEY REFERENCES Customers(ID) ON UPDATE CASCADE  ON DELETE NO ACTION, 
 SaleAmount MONEY,
)

Now, we will populate some data into this table:

现在，我们将一些数据填充到该表中：

INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (1,CAST('05-Mar-2019' AS DATETIME),1,726.24)
INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (2,CAST('02-Nov-2019' AS DATETIME),2,817.33)
INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (3,CAST('13-Nov-2019' AS DATETIME),3,768.02)

When we try to delete one row from the Customers (parent table), we will experience an error.

当我们尝试从客户 （父表）中删除一行时，我们将遇到错误。

DELETE FROM Customers WHERE Id=2

As we can see, the foreign key rule prevents deleting the referenced column value. Now, we will update a row of the Customers (parent table value).

如我们所见，外键规则可防止删除引用的列值。 现在，我们将更新“ 客户”行（父表值）。

DELETE CustomerOrders
SELECT * FROM Customers
SELECT * FROM CustomerSales
UPDATE Customers SET ID=9999 WHERE ID=1
SELECT * FROM Customers
SELECT * FROM CustomerSales

As we can see, updating the Customers (parent table) affects the CustomerSales (child table). Whereas, we did not do any changing on the CustomerSales table.

如我们所见，更新客户 （父表）会影响客户销售 （子表）。 而我们在CustomerSales表上未做任何更改。

结论 (Conclusion)

In this article, we tried to find an answer to “What is a foreign key in the SQL Server” question and we looked at the foreign key concept from different perspectives and gave various answers in the light of this information.

在本文中，我们试图找到“ SQL Server中的外键是什么”问题的答案，我们从不同的角度研究了外键的概念，并根据此信息给出了各种答案。

翻译自: https://www.sqlshack.com/what-is-a-foreign-key-in-sql-server/

sql server 外键