Oluşturma, değiştirme ve yabancı anahtarları kaldırma
De SQL ServerManagement Objects (smo), yabancı anahtarlar gösterilir ForeignKeynesnesini.
smo yabancı anahtar oluşturmak için hangi yabancı anahtar yapıcısı içinde tanımlı tablo belirtmelisiniz ForeignKeynesnesini. Tablodan, yabancı anahtar olarak en az bir sütun seçmelisiniz. Bunu yapmak için create a ForeignKeyColumnnesne değişkeni ve yabancı anahtar sütun adı belirtin. Ardından başvurulan tabloyu ve başvurulan sütun belirtin. Kullanım Addsütun eklemek yöntemi Columnsnesne özelliği.
Yabancı anahtar temsil sütunları listelenen Columnsnesne özelliği ForeignKeynesnesini. Yabancı anahtar tarafından başvurulan birincil anahtarı temsil edilir ReferencedKeybelirtilen tablodaki özellik ReferencedTableözellik.
Örnek
Sunulan kod örneklerinden herhangi birini kullanmak için, programlama ortamını, programlama şablonunu ve uygulamanızı oluşturacağınız programlama dilini seçmeniz gerekecektir. Daha fazla bilgi için, bkz. Visual Studio'da Visual Basic smo proje oluşturun.NET veya Visual Studio'da Visual C# smo proje oluşturun.NET.
Oluşturma, değiştirme ve Visual Basic'te bir yabancı anahtar kaldırma
Bu kod örneği arasında başka bir tablodaki birincil anahtar sütunu için bir tablodaki bir veya daha çok sütun yabancı anahtar ilişkisi oluşturulması gösterilmiştir.
'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()
Oluşturma, değiştirme ve yabancı anahtar Visual C# içinde kaldırma
Bu kod örneği arasında başka bir tablodaki birincil anahtar sütunu için bir tablodaki bir veya daha çok sütun yabancı anahtar ilişkisi oluşturulması gösterilmiştir.
{
//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();
}
{
//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();
}
Oluşturma, değiştirme ve yabancı anahtar PowerShell kaldırma
Bu kod örneği arasında başka bir tablodaki birincil anahtar sütunu için bir tablodaki bir veya daha çok sütun yabancı anahtar ilişkisi oluşturulması gösterilmiştir.
# 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()
# 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()
Örnek: Yabancı anahtarları, birincil anahtar ve benzersiz kısıtlama sütunlar
Bu örnek gösteriyor:
Yabancı anahtar, varolan bir nesne bulma.
Nasıl bir birincil anahtar oluşturmak için.
Nasıl benzersiz kısıtlama sütun oluşturma.
Bu örnek C# sürüm:
// 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);
}
}
}
}
// 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);
}
}
}
}
Örnek Visual Basic sürümü:
' 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
' 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