sql组合键设置外键_学习SQL：外键

sql组合键设置外键

In the previous article, we talked about the primary key (PK). In this one, we’ll check what is the foreign key (FK) and how it’s related to the primary key. This is the final step, after creating tables, inserting data, and defining primary keys, to cover all basics related to the database theory. There is much, much more than this, but if you understand these concepts, everything else is just expanding your knowledge around these basics.

在上一篇文章中，我们讨论了主键（PK）。 在这一部分中，我们将检查什么是外键（FK）以及它与主键的关系。 这是创建表，插入数据并定义主键之后的最后一步，以涵盖与数据库理论有关的所有基础知识。 不仅限于此，但如果您了解这些概念，那么其他所有内容都将使您对这些基础知识有所了解。

外键图形表示 (Foreign key graphical representation)

Before we define what FK is, we’ll once more remind ourselves of what is the primary key:

在定义FK是什么之前，我们将再次提醒自己什么是主键：

“Primary key is a value, or a combination of few values from the table, uniquely defining each record in this table. If we know this value/combination, we can easily find the related record and access all remaining values from that record.”

“主键是一个值，或表中几个值的组合，唯一地定义了该表中的每个记录。 如果我们知道此值/组合，则可以轻松找到相关记录并访问该记录中的所有剩余值。”

In our model, both tables, country, and city have 1 attribute which is used as a primary key and that is the id attribute. The value of this attribute is defined to start from 1 and increment by 1 for each new row added. That way, the system automatically ensures that we don’t have duplicate values.

在我们的模型中， country和city这两个表都有1个用作主键的属性，即id属性。 此属性的值定义为从1开始，并为每个新添加的行增加1。 这样，系统会自动确保我们没有重复的值。

The one thing which is pretty obvious is that we have a line connecting our tables. This line goes from country.id to city.country_id and there is a good reason for that. This is how we graphically represent a FK.

很明显的一件事是，我们有一条连接表的线。 该行从country.id到city.country_id ，这是有充分理由的。 这就是我们以图形方式表示FK的方式。

什么是外键？ (What is a foreign key?)

In the previous section, we concluded that there is some kind of relationship between our two tables. The attribute from one table (city.country_id) is related to the primary key attribute from another table (country.id). That attribute (city.country_id) is a FK attribute. The table country is the referenced table, and the table city is the child table. This relationship actually says that value stored in the city.country_id attribute should be one from the set of values stored in the country.id attribute (or, in some cases, although not this one, could be undefined = NULL).

在上一节中，我们得出结论，我们的两个表之间存在某种关系。 一个表（ city.country_id ）中的属性与另一表（ country.id ）中的主键属性相关。 该属性（ city.country_id ）是FK属性。 表国家/地区是被引用的表，表城市是子表。 这种关系实际上表明，存储在city.country_id属性中的值应该是来自存储在country.id属性中的值集合中的一个（或者，在某些情况下，虽然不是这个，但可能是undefined = NULL）。

So, the definition of the FK would be:

因此，FK的定义为：

“The foreign key is an attribute, or more of them, directly related to the primary key of another table. When properly set, this rule shall ensure that we must always set the value of that attribute to exactly 1 value from the referenced table. This is the way how we relate data stored in different tables in our database model.”

“外键是一个或多个与另一个表的主键直接相关的属性。 正确设置后，此规则应确保我们必须始终将该属性的值设置为引用表中的恰好1个值。 这就是我们如何关联存储在数据库模型中不同表中的数据的方式。”

Note: If primary keys are always defined as a single column, then each FK shall be exactly 1 attribute. While this might not look so important at first, it actually proves to be a good “rule” to follow when designing a database. We’ll talk more about that in the next article related to indices.

注意：如果主键总是定义为单列，则每个FK都应为1属性。 尽管乍一看这似乎并不重要，但实际上在设计数据库时遵循它是一个很好的“规则”。 在下一篇与索引有关的文章中，我们将详细讨论。

外键–如何在数据库中存储/定义外键？ (Foreign key – How it’s stored/defined in the database?)

After the theory, it’s a good time to take a look at when is actually in the database. We’ll do it using the database we’ve created previously (same diagram as the one used in the first section of this article):

根据理论，现在是时候查看数据库中的实际时间。 我们将使用之前创建的数据库来完成此操作（与本文第一部分中使用的图相同）：

After I clicked on the relationship city_country in Object explorer, the Foreign Key Relationship window popped up. Under General -> Tables And Columns Specification you can see the previously mentioned relation between 2 columns in out 2 tables. If we scroll down a little, we can also see the remaining properties:

在“对象资源管理器”中单击关系city_country之后，弹出“外键关系”窗口。 在常规 -> 表和列规范下，您可以看到前面提到的2个表中2列之间的关系。 如果向下滚动一点，我们还可以看到其余的属性：

You can see the following settings:

您可以看到以下设置：

• Enforce for replication – possible values here are “Yes” and “No” – Enables or disables it for replication. Please read more about the replication in SQL Server Replication (Merge) – What gets replicated and what doesn’t article
• 强制进行复制 -此处的可能值为“是”和“否”-启用或禁用复制。 请阅读有关SQL Server复制（合并）中的复制的更多信息-复制什么，什么不复制
• Enforce Foreign Key Constraint – possible values are “Yes” and “No”. If it’s enabled, the FK constraint works as we want it. We could disable it if we want, but we should have a good reason for that. One could be that we want to manually change keys values and we wouldn’t be able to do that because constraints would “scream” all the time 强制执行外键约束 -可能的值为“是”和“否”。 如果启用，则FK约束将按我们期望的方式工作。 如果需要，我们可以禁用它，但是我们应该有充分的理由。 一种可能是我们想要手动更改键值，而我们将无法做到这一点，因为约束会一直“尖叫”
• Delete Rule – possible values are “No Action”, “Cascade”, “Set NULL”, and “Set Default” – defines what will happen with child table rows if we delete the related row from the referenced table 删除规则 –可能的值为“ No Action”，“ Cascade”，“ Set NULL”和“ Set Default” –定义了如果我们从引用表中删除相关行，子表行将会发生什么
• Update Rule – possible values are “No Action”, “Cascade”, “Set NULL”, and “Set Default” – defines what will happen with child table rows if we update the related row in the referenced table 更新规则 –可能的值为“无操作”，“级联”，“设置空值”和“设置默认值” –定义如果我们更新引用表中的相关行，子表行将发生什么

Now we’ll examine the code to see how the FK was defined. Let’s right-click on the table dbo.city and then Script Table as | CREATE to | New Query Editor Window just as on the picture below:

现在，我们将检查代码以了解FK的定义方式。 让我们右键单击表dbo.city ，然后右键单击Script Table as | 创建到 | 新的查询编辑器窗口如下图所示：

The result is the following code:

结果是以下代码：

USE [our_first_database]
GO
    
/****** Object:  Table [dbo].[city]    Script Date: 12/25/2019 4:54:11 PM ******/
SET ANSI_NULLS ON
GO
    
SET QUOTED_IDENTIFIER ON
GO
    
CREATE TABLE [dbo].[city](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [city_name] [char](128) NOT NULL,
    [lat] [decimal](9, 6) NOT NULL,
    [long] [decimal](9, 6) NOT NULL,
    [country_id] [int] NOT NULL,
 CONSTRAINT [city_pk] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
    
ALTER TABLE [dbo].[city]  WITH CHECK ADD  CONSTRAINT [city_country] FOREIGN KEY([country_id])
REFERENCES [dbo].[country] ([id])
GO
    
ALTER TABLE [dbo].[city] CHECK CONSTRAINT [city_country]
GO

You can see FK definitions in the red lines, with the first statement defining which attributes are included in the check and the second statement telling us this is related to the definition of the city table.

您可以在红线中看到FK定义，第一条语句定义检查中包括哪些属性，第二条语句告诉我们这与城市表的定义有关。

外键–它实际上是做什么的？ (Foreign key – What it actually does?)

First, let’s see the contents of our tables:

首先，让我们看一下表的内容：

FK will do a few checks, but most important are:

FK将进行一些检查，但最重要的是：

• If you add a row to a table, the attribute which is part of the foreign key must have the pair in the original table (1:n). It can be NULL if the type of that relation (FK) is not mandatory (0:n). In any case, it can’t contain a value not existing in the referenced table

  如果在表中添加一行，则作为外键一部分的属性必须在原始表中具有该对 （1：n）。 如果该关系的类型（FK）不是强制性的（0：n），则可以为NULL。 无论如何，它不能包含引用表中不存在的值

  In our case, this means, that we can’t add a city if the country_id is not in the range [1,5] – set of id values from the country table. If we try to execute something like this (notice that in this statement country_id = 6 and we don’t have a pair in the country table):

  在本例中，这意味着，如果country_id不在[1,5]范围内（ 国家表中的ID值集），我们就无法添加城市 。 如果我们尝试执行类似的操作（请注意，在该语句中country_id = 6并且在国家/地区表中没有一对）：

  INSERT INTO city (city_name, lat, long, country_id) VALUES ('Wien', 48.2084885, 16.3720798, 6);
  

  This is what happens:

  这是发生了什么：

  And this is great because the FK prevented us from making a mistake

  这很棒，因为FK阻止了我们犯错

  Note: In case we could execute this INSERT statement, we would insert a city belonging to a country with id = 6, without actually knowing that country. Later, someone could insert e.g. the Netherlands as the country with id = 6 and we would have inconsistent data

  注意：如果可以执行此INSERT语句，我们将插入一个ID为6的国家/地区的城市，而实际上并不知道该国家/地区。 后来，有人可以插入例如荷兰作为id = 6的国家，我们的数据将不一致

• When we delete data from the referenced table, we won’t be able to delete if there are related records in the child table. This stands when setting Delete Rule is set to “No Action” (this should be how you set your foreign key in most cases). “Cascade” would also delete rows from the child table, while “Set NULL” and “Set Default” wouldn’t delete entire rows from the child table, but just set the values of these attributes to NULL or predefined value

  当我们从引用表中删除数据时，如果子表中有相关​​记录，我们将无法删除。 当将“ 删除规则 ”设置为“无操作”时（在大多数情况下，这应是您设置外键的方式），即表示正确。 “级联”也会从子表中删除行，而“设置为空”和“设置默认值”不会从子表中删除整行，而只是将这些属性的值设置为NULL或预定义值

  Let’s try to delete country with id = 5 (Poland) using the statement:

  让我们尝试使用以下语句删除id = 5（波兰）的国家/地区：

  DELETE FROM country WHERE id = 5;
  

  The result is shown below:

  结果如下所示：

  Once again, this is really great because the defined rule (FK), prevented us from deleting something we still use

  再一次，这确实很棒，因为定义的规则（FK）阻止我们删除仍在使用的内容

  Note: If the statement would delete Poland, then Warsaw would be assigned to the non-existing country. While we don’t know which country it belongs to, someone could later insert another country with id = 5 (as for inserts) and we would have a problem with our data.

  注意：如果该声明将删除波兰，则华沙将分配给不存在的国家。 虽然我们不知道它属于哪个国家，但后来有人可以插入另一个ID = 5的国家（关于插入），我们的数据就会出现问题。

The FK, when defined properly, instead of you, does the job in the background. It takes care of the referential integrity in our database.

如果定义正确，FK会代替您在后台执行作业。 它照顾了我们数据库中的参照完整性。

结论 (Conclusion)

Foreign keys, together with primary keys, relate data throughout our database. Keys are usually not data from the real-world (we actually have an interest only in the real-world data), but the values we generate automatically. Still, they are needed in order that everything runs (smoothly). This topic definitely requires more than 1 article and today we just scratched the surface. In order to explain this concept, we’ll talk about the types of relations, referential integrity, and indices in upcoming articles.

外键与主键一起在整个数据库中关联数据。 键通常不是来自真实世界的数据（实际上，我们仅对真实世界的数据感兴趣），而是我们自动生成的值。 尽管如此，它们仍是必需的，以便一切（顺利）运行。 这个主题肯定需要多篇文章，而今天我们才刚刚开始。 为了解释这个概念，我们将在以后的文章中讨论关系的类型，参照完整性和索引。

目录 (Table of contents)

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions

学习SQL：CREATE DATABASE＆CREATE TABLE操作
学习SQL：插入表
学习SQL：主键
学习SQL：外键
学习SQL：SELECT语句
学习SQL：INNER JOIN与LEFT JOIN
学习SQL：SQL脚本
学习SQL：关系类型
学习SQL：联接多个表
学习SQL：聚合函数
学习SQL：如何编写复杂的SELECT查询
学习SQL：INFORMATION_SCHEMA数据库
学习SQL：SQL数据类型
学习SQL：集合论
学习SQL：用户定义的函数
学习SQL：用户定义的存储过程
学习SQL：SQL视图
学习SQL：SQL触发器
学习SQL：练习SQL查询
学习SQL：SQL查询示例
学习SQL：使用SQL查询手动创建报告
学习SQL：SQL Server日期和时间函数
学习SQL：使用日期和时间函数创建SQL Server报表
学习SQL：SQL Server数据透视表
学习SQL：将SQL Server导出到Excel
学习SQL：SQL Server循环简介
学习SQL：SQL Server游标
学习SQL：删除和更新数据SQL最佳实践
学习SQL：命名约定

翻译自: https://www.sqlshack/learn-sql-foreign-key/

sql组合键设置外键