sql外键

In this article let us review different ways to create a SQL foreign key, rules on updates and deletes, enabling foreign key constraints, disabling foreign key constraints and not for replication in foreign keys.

在本文中，我们回顾了创建SQL外键，更新和删除规则，启用外键约束，禁用外键约束以及不用于外键复制的不同方法。

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

A Foreign key is constraint that enforces referential integrity in SQL server database. It uses a column or combination of columns that is used establish link between the data in two tables to control the data that can be stored in the foreign key table.

外键是在SQL Server数据库中强制引用完整性的约束。 它使用一列或多列组合，用于在两个表中的数据之间建立链接，以控制可存储在外键表中的数据。

创建外键约束 (Creating a Foreign key constraint)

To create a SQL foreign key constraint, the parent table should have primary key column or column with UNIQUE constraint.

要创建SQL外键约束，父表应具有主键列或具有UNIQUE约束的列。

In this case, table Dept is parent table which has Primary key and will be referenced in child tables having foreign key.

在这种情况下，表Dept是具有主键的父表，将在具有外键的子表中被引用。

CREATE TABLE Dept (  DeptID INT CONSTRAINT PK_Dept PRIMARY KEY  ,DeptName VARCHAR(10)  )

创建表时创建外键 (Create a foreign key while creating a table)

Below is the example of a T-SQL script to create a SQL foreign key while creating the table without defining the constraint name. If we do not specify the name of constraint, SQL server creates foreign key constraint with random name.

以下是在不定义约束名称的情况下创建表时创建SQL外键的T-SQL脚本示例。 如果未指定约束的名称，则SQL Server将使用随机名称创建外键约束。

CREATE TABLE Emp (  EmpID INT  ,EmpFname VARCHAR(50)  ,EmpLname VARCHAR(50)  ,DeptID INT  FOREIGN KEY REFERENCES  Dept(DeptID)  ,IsActive BIT  )

Below is the example of a T-SQL script to create a foreign key while creating the table with specific constraint name.

以下是在创建具有特定约束名称的表时创建外键的T-SQL脚本示例。

CREATE TABLE Emp (  EmpID INT  ,EmpFname VARCHAR(50)  ,EmpLname VARCHAR(50)  ,DeptID INT CONSTRAINT FK_Emp FOREIGN KEY REFERENCES  Dept(DeptID)  ,IsActive BIT  )

创建表后创建外键 (Create a foreign key after creating a table)

Let us create the child table first and create a SQL foreign key constraint later. Below is example of creating foreign key constraint after creating table by specifying the constraint name.

让我们先创建子表，然后再创建SQL外键约束。 下面是通过指定约束名称在创建表之后创建外键约束的示例。

CREATE TABLE Emp (  EmpID INT  ,EmpFname VARCHAR(50)  ,EmpLname VARCHAR(50)  ,DeptID INT   ,IsActive BIT  )   ALTER TABLE [dbo].[Emp]  WITH CHECK ADD  CONSTRAINT [FK_Emp] FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID])GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp]GO

Below is example of creating foreign key after creating table without specifying the constraint name.

以下是在不指定约束名称的情况下创建表后创建外键的示例。

ALTER TABLE [dbo].[Emp]  WITH CHECK ADD  FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID])GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp]GO

创建外键而不检查现有数据 (Create a foreign key without checking for an existing data)

In a few cases, the referencing table may already exist and have data which violates the SQL foreign key constraint you are going to create.

在某些情况下，引用表可能已经存在并且包含违反您要创建SQL外键约束的数据。

If we create a constraint with a check it will throw the below error as the data which already exist is violating the rule.

如果我们通过检查创建约束，则将抛出以下错误，因为已经存在的数据违反了规则。

If you still want to create a foreign key constraint by ignoring the existing data and validate the rule for further changes use “WITH NOCHECK”. The constraint you created is marked as not trusted.

如果您仍然想通过忽略现有数据来创建外键约束并验证规则以进行进一步更改，请使用“ WITH NOCHECK”。 您创建的约束被标记为不可信。

ALTER TABLE [dbo].[Emp]  WITH NOCHECK ADD  CONSTRAINT [FK_Emp] FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID])GO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp]GO

使用DELETE / UPDATE规则创建外键 (Create a foreign key with DELETE/UPDATE rules)

We can create a SQL foreign key constraint by specifying the what action to happen on referencing table when delete and update happens on primary key in parent table. Let us see some scenarios.

我们可以通过指定在父表中的主键上进行删除和更新时对引用表执行什么操作来创建SQL外键约束。 让我们看看一些情况。

We can see the data exist in both parent and child table.

我们可以看到数据存在于父表和子表中。

Let us create a foreign key with delete cascade and delete the primary key value in parent table.

让我们创建一个带有删除级联的外键，并删除父表中的主键值。

ALTER TABLE [dbo].[Emp]  WITH CHECK ADD  CONSTRAINT [FK_Emp2] FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID]) ON DELETE CASCADEGO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp2]GO  delete from Dept where DeptID =1

After executing the above statement, we can see the that data is deleted in the child table as well.

执行完上面的语句后，我们可以看到子表中的数据也被删除了。

Let us create a foreign key with update cascade and update the primary key in the parent table.

让我们用更新级联创建一个外键并更新父表中的主键。

ALTER TABLE [dbo].[Emp]  WITH CHECK ADD  CONSTRAINT [FK_Emp3] FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID])ON UPDATE CASCADEGO ALTER TABLE [dbo].[Emp] CHECK CONSTRAINT [FK_Emp3]GO update Dept set DeptID =3 where DeptID=2

After executing the above statement, we can see DeptID is updated to 3 for records having DeptID 2 in the child table as well.

执行完上面的语句后，我们可以看到子表中DeptID为2的记录的DeptID也更新为3。

Similarly, we have the following actions.

同样，我们有以下操作。

• SET NULL – Sets the SQL foreign key column value to null when the primary key value is either deleted or updated to a new value. If the column does not allow null values, the update/delete on primary key column fails and throw error

  SET NULL –当主键值被删除或更新为新值时，将SQL外键列值设置为null。 如果该列不允许使用空值，则主键列上的更新/删除失败并抛出错误

• NO ACTION – If the update or delete on primary key column fails the constraint rule then the change is rolled back
  无操作–如果对主键列的更新或删除未通过约束规则，则将回滚更改

To modify the existing foreign key using T-SQL we must drop the foreign key constraint first and then re create it with new changes.

要使用T-SQL修改现有外键，我们必须先删除外键约束，然后使用新的更改重新创建它。

禁用和启用外键约束 (Disabling and enabling the foreign key constraint)

禁用约束 (Disable constraint)

To disable a SQL foreign key constraint, we need to use below statement. Replace the table name and constraint name. When the foreign key constraint is disabled the constraint is marked as not trusted.

要禁用SQL外键约束，我们需要使用以下语句。 替换表名称和约束名称。 禁用外键约束后，该约束将标记为不可信。

ALTER TABLE [Emp] NOCHECK CONSTRAINT [FK_Emp2]

启用约束 (Enable constraint)

To enable the constraint back use below statement.

要启用约束，请使用下面的语句。

ALTER TABLE [Emp] CHECK CONSTRAINT [FK_Emp2]

通过检查现有数据启用约束 (Enable constraint with checking existing data)

To force foreign key to check existing data while enabling the constraint use below statement. In this case if validation is success and constraint is enabled then the constraint is marked back to trusted.

要在启用约束的同时强制外键检查现有数据，请使用以下语句。 在这种情况下，如果验证成功并且启用了约束，则将该约束标记回可信状态。

ALTER TABLE [Emp]  WITH CHECK CHECK CONSTRAINT [FK_Emp2] GO

不用于复制 (Not for replication)

When a foreign key is set “NOT FOR REPELCIATION”, the validation is done only when user inserts, deletes or updates data.

当外键设置为“ NOT FOR REPELCIATION”时，仅当用户插入，删除或更新数据时才进行验证。

The validation is not fired when the replication agents sync the changes made by user to other end (i.e. subscriber in case of transactional and both subscriber and publisher in case of merge replication).

当复制代理将用户所做的更改同步到另一端时（即，在事务复制的情况下为订户，在合并复制的情况下为订户和发布者都将其同步），则不会触发验证。

To create a SQL foreign key with “NOT FOR REPELCIATION”, use below script. By default, the constraint is marked as not trusted.

要使用“ NOT FOR REPELCIATION”创建一个SQL外键，请使用以下脚本。 默认情况下，该约束被标记为不可信。

ALTER TABLE [dbo].[Emp]  WITH CHECK ADD  CONSTRAINT [FK_Emp2]  FOREIGN KEY([DeptID])REFERENCES [dbo].[Dept] ([DeptID]) NOT FOR REPLICATION GO

Even though we create the foreign key with “FOR REPLICATION” on the publisher, the snapshot agent scripts it as “NOT FOR REPLICATION” and foreign key is created as not for replication in subscriber when the snapshot is applied.

即使我们在发布服务器上使用“ FOR REPLICATION”创建外键，快照代理也会将其脚本化为“ NOT FOR REPLICATION”，并且在应用快照时，外键的创建不会在订户中进行复制。

索引外键列 (Indexing foreign keys columns)

When a table is being referenced by a SQL foreign key, modifications on the primary key column in primary table will check the data in the child table. If the child table has a lot of data, it may slow down your change on the primary table. We may even encounter deadlocks in such cases.

当使用SQL外键引用表时，对主表中主键列的修改将检查子表中的数据。 如果子表中有大量数据，则可能会减慢主表上的更改。 在这种情况下，我们甚至可能会遇到僵局。

Creating an index on a foreign key column helps in such cases.

在这种情况下，在外键列上创建索引会有所帮助。

翻译自:

sql外键