--- title: "Creating, Altering, and Removing Foreign Keys | Microsoft Docs" ms.custom: "" ms.date: "06/13/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: ms.topic: "reference" helpviewer_keywords: - "foreign keys [SMO]" ms.assetid: d43c8dca-bb6b-4a41-8a79-c96fd546fc91 author: stevestein ms.author: sstein manager: craigg --- # Creating, Altering, and Removing Foreign Keys In [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] Management Objects (SMO), foreign keys are represented by the object. To create a foreign key in SMO, you must specify the table on which the foreign key is defined in the constructor of the object. From the table, you must select at least one column to be the foreign key. To do this, create a object variable and specify the name of the column that is the foreign key. Then, specify the referenced table and referenced column. Use the method to add the column to the `Columns` object property. The columns that represent the foreign key are listed in the `Columns` object property of the object. The primary key that is referenced by the foreign key is represented by the property that is in the table specified in the property. ## Example To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see [Create a Visual Basic SMO Project in Visual Studio .NET](../../../database-engine/dev-guide/create-a-visual-basic-smo-project-in-visual-studio-net.md) or [Create a Visual C# SMO Project in Visual Studio .NET](../how-to-create-a-visual-csharp-smo-project-in-visual-studio-net.md). ## Creating, Altering, and Removing a Foreign Key in Visual Basic This code example shows how to create a foreign key relationship between one or more columns in one table to a primary key column in another table. ## Creating, Altering, and Removing a Foreign Key in Visual C# This code example shows how to create a foreign key relationship between one or more columns in one table to a primary key column in another table. ```csharp { //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(); //Reference the AdventureWorks2012 database. Database db; db = srv.Databases["AdventureWorks2012"]; //Declare another Table object variable and reference the EmployeeDepartmentHistory table. Table tbea; tbea = db.Tables["EmployeeDepartmentHistory", "HumanResources"]; //Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory as the parent table and the foreign key name in the constructor. ForeignKey fk; fk = new ForeignKey(tbea, "test_foreignkey"); //Add BusinessEntityID as the foreign key column. ForeignKeyColumn fkc; fkc = new ForeignKeyColumn(fk, "BusinessEntityID", "BusinessEntityID"); 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(); } ``` ## Creating, Altering, and Removing a Foreign Key in PowerShell This code example shows how to create a foreign key relationship between one or more columns in one table to a primary key column in another table. ```powershell # Set the path context to the local, default instance of SQL Server and to the #database tables in Adventureworks2012 CD \sql\localhost\default\databases\AdventureWorks2012\Tables\ #Get reference to the FK table $tbea = get-item HumanResources.EmployeeDepartmentHistory # Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory # as the parent table and the foreign key name in the constructor. $fk = New-Object -TypeName Microsoft.SqlServer.Management.SMO.ForeignKey ` -argumentlist $tbea, "test_foreignkey" #Add BusinessEntityID as the foreign key column. $fkc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.ForeignKeyColumn ` -argumentlist $fk, "BusinessEntityID", "BusinessEntityID" $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() ``` ## Sample: Foreign Keys, Primary Keys, and Unique Constraint Columns This sample demonstrates: - Finding a foreign key on an existing object. - How to create a primary key. - How to create a unique constraint column. The C# version of this sample: ```csharp // compile with: // /r:Microsoft.SqlServer.Smo.dll // /r:microsoft.sqlserver.management.sdk.sfc.dll // /r:Microsoft.SqlServer.ConnectionInfo.dll // /r:Microsoft.SqlServer.SqlEnum.dll using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Sdk.Sfc; using Microsoft.SqlServer.Management.Common; using System; public class A { public static void Main() { Server svr = new Server(); Database db = new Database(svr, "TESTDB"); db.Create(); // PK Table Table tab1 = new Table(db, "Table1"); // Define Columns and add them to the table Column col1 = new Column(tab1, "Col1", DataType.Int); col1.Nullable = false; tab1.Columns.Add(col1); Column col2 = new Column(tab1, "Col2", DataType.NVarChar(50)); tab1.Columns.Add(col2); Column col3 = new Column(tab1, "Col3", DataType.DateTime); tab1.Columns.Add(col3); // Create the ftable tab1.Create(); // Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor. Index pk = new Index(tab1, "Table1_PK"); pk.IndexKeyType = IndexKeyType.DriPrimaryKey; // Add Col1 as the Index Column IndexedColumn idxCol1 = new IndexedColumn(pk, "Col1"); pk.IndexedColumns.Add(idxCol1); // Create the Primary Key pk.Create(); // Create Unique Index on the table Index unique = new Index(tab1, "Table1_Unique"); unique.IndexKeyType = IndexKeyType.DriUniqueKey; // Add Col1 as the Unique Index Column IndexedColumn idxCol2 = new IndexedColumn(unique, "Col2"); unique.IndexedColumns.Add(idxCol2); // Create the Unique Index unique.Create(); // Create Table2 Table tab2 = new Table(db, "Table2"); Column col21 = new Column(tab2, "Col21", DataType.NChar(20)); tab2.Columns.Add(col21); Column col22 = new Column(tab2, "Col22", DataType.Int); tab2.Columns.Add(col22); tab2.Create(); // Define a Foreign Key object variable by supplying the Table2 as the parent table and the foreign key name in the constructor. ForeignKey fk = new ForeignKey(tab2, "Table2_FK"); // Add Col22 as the foreign key column. ForeignKeyColumn fkc = new ForeignKeyColumn(fk, "Col22", "Col1"); fk.Columns.Add(fkc); fk.ReferencedTable = "Table1"; // Create the foreign key on the instance of SQL Server. fk.Create(); // Get list of Foreign Keys on Table2 foreach (ForeignKey f in tab2.ForeignKeys) { Console.WriteLine(f.Name + " " + f.ReferencedTable + " " + f.ReferencedKey); } // Get list of Foreign Keys referencing table1 foreach (Table tab in db.Tables) { if (tab == tab1) continue; foreach (ForeignKey f in tab.ForeignKeys) { if (f.ReferencedTable.Equals(tab1.Name)) Console.WriteLine(f.Name + " " + f.Parent.Name); } } } } ``` The Visual Basic version of the sample: ```vb ' compile with: ' /r:Microsoft.SqlServer.Smo.dll ' /r:microsoft.sqlserver.management.sdk.sfc.dll ' /r:Microsoft.SqlServer.ConnectionInfo.dll ' /r:Microsoft.SqlServer.SqlEnum.dll Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Sdk.Sfc Imports Microsoft.SqlServer.Management.Common Public Class A Public Shared Sub Main() Dim svr As New Server() Dim db As New Database(svr, "TESTDB") db.Create() ' PK Table Dim tab1 As New Table(db, "Table1") ' Define Columns and add them to the table Dim col1 As New Column(tab1, "Col1", DataType.Int) col1.Nullable = False tab1.Columns.Add(col1) Dim col2 As New Column(tab1, "Col2", DataType.NVarChar(50)) tab1.Columns.Add(col2) Dim col3 As New Column(tab1, "Col3", DataType.DateTime) tab1.Columns.Add(col3) ' Create the ftable tab1.Create() ' Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor. Dim pk As New Index(tab1, "Table1_PK") pk.IndexKeyType = IndexKeyType.DriPrimaryKey ' Add Col1 as the Index Column Dim idxCol1 As New IndexedColumn(pk, "Col1") pk.IndexedColumns.Add(idxCol1) ' Create the Primary Key pk.Create() ' Create Unique Index on the table Dim unique As New Index(tab1, "Table1_Unique") unique.IndexKeyType = IndexKeyType.DriUniqueKey ' Add Col1 as the Unique Index Column Dim idxCol2 As New IndexedColumn(unique, "Col2") unique.IndexedColumns.Add(idxCol2) ' Create the Unique Index unique.Create() ' Create Table2 Dim tab2 As New Table(db, "Table2") Dim col21 As New Column(tab2, "Col21", DataType.NChar(20)) tab2.Columns.Add(col21) Dim col22 As New Column(tab2, "Col22", DataType.Int) tab2.Columns.Add(col22) tab2.Create() ' Define a Foreign Key object variable by supplying the Table2 as the parent table and the foreign key name in the constructor. Dim fk As New ForeignKey(tab2, "Table2_FK") ' Add Col22 as the foreign key column. Dim fkc As New ForeignKeyColumn(fk, "Col22", "Col1") fk.Columns.Add(fkc) fk.ReferencedTable = "Table1" ' Create the foreign key on the instance of SQL Server. fk.Create() ' Get list of Foreign Keys on Table2 For Each f As ForeignKey In tab2.ForeignKeys Console.WriteLine((f.Name + " " + f.ReferencedTable & " ") + f.ReferencedKey) Next ' Get list of Foreign Keys referencing table1 For Each tab As Table In db.Tables If (tab.Name.Equals(tab1.Name)) Then Continue For End If For Each f As ForeignKey In tab.ForeignKeys If f.ReferencedTable.Equals(tab1.Name) Then Console.WriteLine(f.Name + " " + f.Parent.Name) End If Next Next End Sub End Class ```