创建、更改和删除外键

在 SQL Server 管理对象 (SMO) 中，外键由 ForeignKey 对象表示。

若要在 SMO 中创建外键，则必须在 ForeignKey 对象的构造函数中指定在其上定义外键的表。在该表中，必须至少选择一个列作为外键。为此，请创建一个 ForeignKeyColumn 对象变量并指定作为外键的列的名称。然后指定被引用表和被引用列。使用 Add 方法将列添加到 Columns 对象属性。

表示外键的列在 ForeignKey 对象的 Columns 对象属性中列出。外键引用的主键由在 ReferencedTable 属性中指定的表中的 ReferencedKey 属性表示。

示例

若要使用所提供的任何代码示例，您必须选择创建应用程序所需的编程环境、编程模板和编程语言。有关详细信息，请参阅如何在 Visual Studio .NET 中创建 Visual Basic SMO 项目或如何在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中创建、更改和删除外键

此代码示例演示如何在一个表的一个或多个列与另一个表的主键列之间创建外键关系。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Declare a Table object variable and reference the Employee table.
Dim tbe As Table
tbe = db.Tables("Employee", "HumanResources")
'Declare another Table object variable and reference the EmployeeAddress table.
Dim tbea As Table
tbea = db.Tables("EmployeeAddress", "HumanResources")
'Define a Foreign Key object variable by supplying the EmployeeAddress as the parent table and the foreign key name in the constructor.
Dim fk As ForeignKey
fk = New ForeignKey(tbea, "test_foreignkey")
'Add EmployeeID as the foreign key column.
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, "EmployeeID", "EmployeeID")
fk.Columns.Add(fkc)
'Set the referenced table and schema.
fk.ReferencedTable = "Employee"
fk.ReferencedTableSchema = "HumanResources"
'Create the foreign key on the instance of SQL Server.
fk.Create()

在 Visual C# 中创建、更改和删除外键

此代码示例演示如何在一个表的一个或多个列与另一个表的主键列之间创建外键关系。

{ 
//Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db; 
db = srv.Databases("AdventureWorks"); 
//Declare a Table object variable and reference the Employee table. 
Table tbe; 
tbe = db.Tables("Employee", "HumanResources"); 
//Declare another Table object variable and reference the EmployeeAddress table. 
Table tbea; 
tbea = db.Tables("EmployeeAddress", "HumanResources"); 
//Define a Foreign Key object variable by supplying the EmployeeAddress as the parent table and the foreign key name in the constructor. 
ForeignKey fk; 
fk = new ForeignKey(tbea, "test_foreignkey"); 
//Add EmployeeID as the foreign key column. 
ForeignKeyColumn fkc; 
fkc = new ForeignKeyColumn(fk, "EmployeeID", "EmployeeID"); 
fk.Columns.Add(fkc); 
//Set the referenced table and schema. 
fk.ReferencedTable = "Employee"; 
fk.ReferencedTableSchema = "HumanResources"; 
//Create the foreign key on the instance of SQL Server. 
fk.Create();
}