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中的外鍵是什么？”。 同時，我們將對此問題給出一些補充答案。 這樣，我們可以更清楚地了解外鍵概念。
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.
外鍵是允許我們在兩個表中的數據之間建立引用鏈接的一列或一組列。 該引用鏈接有助于將外鍵列數據與引用表數據的數據進行匹配。 被引用的表稱為父表，而涉及外鍵的表稱為子表。 此外，如果外鍵引用同一表的另一列，則此引用類型稱為自引用。
sql外鍵是什么意思。 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中的外鍵是什么”問題。 現在，我們將通過一些示例來了解外鍵設計模型和使用細節。
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.
sql外鍵的作用、 此外鍵在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:
sql server的主要功能？ 首先，我們將通過以下查詢創建一個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]
sql server有什么用。 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)
sqlserver數據庫， 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)
mysql外鍵，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 FKINNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID
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.
sqlserver存儲過程？ 提示： 什么是SQL Server中的外鍵：外鍵的主要目的是建立對將插入表的數據的控制，其中涉及外鍵。 插入的數據必須與引用的表匹配。
These rules are:
這些規則是：
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??INTPRIMARY 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:
sql server和mysql？ 現在，我們將一些數據填充到該表中：
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
sqlserver安裝，
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表上未做任何更改。
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外鍵、sql server 外鍵
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
