Creazione, modifica e rimozione di chiavi esterneCreating, Altering, and Removing Foreign Keys

QUESTO ARGOMENTO SI APPLICA A: sìSQL ServersìDatabase SQL di Microsoft AzuresìMicrosoft Azure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse noParallel Data Warehouse

In SMO ( SQL ServerSQL Server Management Objects) le chiavi esterne sono rappresentate dall'oggetto ForeignKey.In SQL ServerSQL Server Management Objects (SMO), foreign keys are represented by the ForeignKey object.

Per creare una chiave esterna in SMO, è necessario specificare la tabella in cui la chiave esterna viene definita nel costruttore del ForeignKey oggetto.To create a foreign key in SMO, you must specify the table on which the foreign key is defined in the constructor of the ForeignKey object. Dalla tabella, è necessario selezionare almeno una colonna che rappresenti la chiave esterna.From the table, you must select at least one column to be the foreign key. A questo scopo, creare una variabile dell'oggetto ForeignKeyColumn e specificare il nome della colonna che rappresenta la chiave esterna.To do this, create a ForeignKeyColumn object variable and specify the name of the column that is the foreign key. Specificare quindi la tabella e la colonna a cui si fa riferimento.Then, specify the referenced table and referenced column. Utilizzare il Add metodo al quale aggiungere la colonna di colonne proprietà dell'oggetto.Use the Add method to add the column to the Columns object property.

Le colonne che rappresentano la chiave esterna sono elencate nel colonne proprietà dell'oggetto di ForeignKey oggetto.The columns that represent the foreign key are listed in the Columns object property of the ForeignKey object. La chiave primaria a cui fa riferimento la chiave esterna è rappresentata dal ReferencedKey nella tabella specificata nella proprietà di ReferencedTable proprietà.The primary key that is referenced by the foreign key is represented by the ReferencedKey property that is in the table specified in the ReferencedTable property.

EsempioExample

Per usare qualsiasi esempio di codice fornito, è necessario scegliere l'ambiente di programmazione, il modello di programmazione e il linguaggio di programmazione per la creazione dell'applicazione.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. Per altre informazioni, vedere creare un Visual C# progetto SMO in Visual Studio .NET.For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Creazione, modifica e rimozione di una chiave esterna in Visual BasicCreating, Altering, and Removing a Foreign Key in Visual Basic

In questo esempio di codice viene illustrato come creare una relazione di chiave esterna tra una o più colonne in una tabella e una colonna chiave primaria in un'altra tabella.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.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'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 EmployeeDepartmentHistory table.
Dim tbea As Table
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.
Dim fk As ForeignKey
fk = New ForeignKey(tbea, "test_foreignkey")
'Add BusinessEntityID as the foreign key column.
Dim fkc As ForeignKeyColumn
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()

Creazione, modifica e rimozione di una chiave esterna in Visual C#Creating, Altering, and Removing a Foreign Key in Visual C#

In questo esempio di codice viene illustrato come creare una relazione di chiave esterna tra una o più colonne in una tabella e una colonna chiave primaria in un'altra tabella.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.

{  
            //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();  
        }  

Creazione, modifica e rimozione di una chiave esterna in PowerShellCreating, Altering, and Removing a Foreign Key in PowerShell

In questo esempio di codice viene illustrato come creare una relazione di chiave esterna tra una o più colonne in una tabella e una colonna chiave primaria in un'altra tabella.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.

# 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()  

Esempio: chiavi esterne, chiavi primarie e colonne con vincolo univocoSample: Foreign Keys, Primary Keys, and Unique Constraint Columns

In questo esempio vengono illustrate le operazioni seguenti:This sample demonstrates:

  • Individuazione di una chiave esterna in un oggetto esistente.Finding a foreign key on an existing object.

  • Modalità di creazione di una chiave primaria.How to create a primary key.

  • Modalità di creazione di una colonna con vincolo univoco.How to create a unique constraint column.

    Versione C# di questo esempio:The C# version of this sample:

// 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);  
            }  
      }  
   }  
}  

Versione Visual Basic dell'esempio:The Visual Basic version of the sample:

' 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