首页 / C# / 如何在C#中为表设置外键？
如何在C#中为表设置外键？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何在C#中为表设置外键？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11546字，纯文字阅读大概需要17分钟。
内容图文
我正在开发一个程序,该程序创建数据库并构建维度表和事实表.我在设置外键和约束时遇到麻烦.
这是我要执行的实际SQL：
CREATE TABLE FactSalesOrders
(ProductKey int NOT NULL REFERENCES DimProduct(ProductKey),
CustomerKey int NOT NULL REFERENCES DimCustomer(CustomerKey),
SalespersonKey int NOT NULL REFERENCES DimSalesperson(SalepersonKey),
OrderDateKey int NOT NULL REFERENCES DimDate(DateKey),
OrderNo int NOT NULL,
ItemNo int Not NULL,
Quantity int Not NULL,
SalesAmount money NOT NULL,
Cost money NOT NULL
CONSTRAINT [PK_FactSalesOrders] PRIMARY KEY NONCLUSTERED
(
[ProductKey],[CustomerKey],[SalespersonKey],[OrderDateKey],[OrderNo],[ItemNo]
)
)
这是事实表：
//Creating Fact Table
Table factTable = new Table(myDatabase, "Fact Table");
//Column One: Product Key
Column ProductKey = new Column(factTable, "ProductKey", DataType.Int);
ProductKey.Nullable = false;
factTable.Columns.Add(ProductKey);
//Column Two: Customer Key
Column CustomerKey = new Column(factTable, "CustomerKey", DataType.Int);
CustomerKey.Nullable = false;
factTable.Columns.Add(CustomerKey);
//Column Three: Sales Person Key
Column SalespersonKey = new Column(factTable, "SalespersonKey", DataType.Int);
SalespersonKey.Nullable = false;
factTable.Columns.Add(SalespersonKey);
//Column Four: Order Date Key
Column OrderDateKey = new Column(factTable, "OrderDateKey", DataType.Int);
OrderDateKey.Nullable = false;
factTable.Columns.Add(OrderDateKey);
//Column Five: Order Number
Column OrderNo = new Column(factTable, "OrderNo", DataType.Int);
OrderNo.Nullable = false;
factTable.Columns.Add(OrderNo);
//Column Six: Item Number
Column ItemNo = new Column(factTable, "ItemNo", DataType.Int);
ItemNo.Nullable = false;
factTable.Columns.Add(ItemNo);
//Column Seven: Quantity
Column Quantity = new Column(factTable, "Quantity", DataType.Int);
Quantity.Nullable = false;
factTable.Columns.Add(Quantity);
//Column Eight: Sales Amount
Column SalesAmount = new Column(factTable, "SalesAmount", DataType.Money);
SalesAmount.Nullable = false;
factTable.Columns.Add(SalesAmount);
//Column Nine: Cost
Column Cost = new Column(factTable, "Cost", DataType.Money);
Cost.Nullable = false;
factTable.Columns.Add(Cost);
factTable.Create();
这是所有的C#代码(注意：事实表在底部)：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.Data;
namespace Hillstar_Project
{
class Program
{
static void Main(string[] args)
{
Server myServer = new Server(@"localhost");
Boolean exit = false;
while (exit == false)
{
Console.Write("Enter Name Of Data Warehouse: ");
String NewDatabaseName = Console.ReadLine();
SqlConnection myConnection = new SqlConnection("user id=app;" +
"password=test;server=localhost;" +
"Trusted_Connection=yes;" +
"database=test; " +
"connection timeout=30");
try
{
myConnection.Open();
Console.WriteLine("Successful Connection");
try
{
//DatabaseObjectCreation(myServer);
DataWarehouseCreation(myServer, NewDatabaseName);
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
Console.Write("Exit? (y/n): ");
if (Console.ReadLine() == "y")
{
exit = true;
}
else
{
exit = false;
}
}
}
private static void DataWarehouseCreation(Server myServer, String NewDatabaseName)
{
//Drop the database if it exists
if (myServer.Databases[NewDatabaseName] != null){
Console.Write("Are you sure you want to override existing database? (y/n): ");
if(Console.ReadLine() == "y"){
myServer.Databases[NewDatabaseName].Drop();
}
else{
Console.WriteLine("Database was not overwritten...");
return;
}
}
//Create database
Database myDatabase = new Database(myServer, NewDatabaseName);
myDatabase.Create();
//Creating DimProduct
Table DimProduct = new Table(myDatabase, "DimProduct");
//Column One: Product Key
Column productKey = new Column(DimProduct, "ProductKey", DataType.Int);
productKey.Nullable = false;
DimProduct.Columns.Add(productKey);
//Column Two: Product Alt Key
Column productAltKey = new Column(DimProduct, "ProductAltKey", DataType.NVarChar(10));
productAltKey.Nullable = false;
DimProduct.Columns.Add(productAltKey);
//Column Three: Product Name
Column productName = new Column(DimProduct, "ProductName", DataType.NVarChar(50));
productName.Nullable = true;
DimProduct.Columns.Add(productName);
//Column Four: Product Description
Column productDescription = new Column(DimProduct, "ProductDescription", DataType.NVarChar(100));
productDescription.Nullable = true;
DimProduct.Columns.Add(productDescription);
//Column Five: Product Catagory Name
Column productCatagoryName = new Column(DimProduct, "ProductCatagoryName", DataType.NVarChar(50));
productCatagoryName.Nullable = true;
DimProduct.Columns.Add(productCatagoryName);
//Primary Key
Index primaryKeyIndex1 = new Index(DimProduct, "PK_DimProduct");
primaryKeyIndex1.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex1.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex1, "productKey"));
DimProduct.Indexes.Add(primaryKeyIndex1);
DimProduct.Create();
Console.WriteLine("DimProduct Table Created");
//Creating DimCustomer
Table DimCustomer = new Table(myDatabase, "DimCustomer");
//Column One: Customer Key
Column customerKey = new Column(DimCustomer, "CustomerKey", DataType.Int);
productKey.Nullable = false;
DimCustomer.Columns.Add(customerKey);
//Column Two: Customer Alt Key
Column customerAltKey = new Column(DimCustomer, "CustomerAltKey", DataType.NVarChar(10));
productAltKey.Nullable = false;
DimCustomer.Columns.Add(customerAltKey);
//Column Three: Customer Name
Column customerName = new Column(DimCustomer, "CustomerName", DataType.NVarChar(50));
customerName.Nullable = true;
DimCustomer.Columns.Add(customerName);
//Column Four: Customer Email
Column customerEmail = new Column(DimCustomer, "CustomerEmail", DataType.NVarChar(50));
customerEmail.Nullable = true;
DimCustomer.Columns.Add(customerEmail);
//Column Five: Customer Geography Key
Column customerGeographyKey = new Column(DimCustomer, "CustomerGeographyKey", DataType.Int);
customerGeographyKey.Nullable = true;
DimCustomer.Columns.Add(customerGeographyKey);
//Primary Key
Index primaryKeyIndex2 = new Index(DimCustomer, "PK_DimCustomer");
primaryKeyIndex2.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex2.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex2, "customerKey"));
DimCustomer.Indexes.Add(primaryKeyIndex2);
DimCustomer.Create();
Console.WriteLine("DimCustomer Table Created");
//Creating DimSalesPerson
Table DimSalesperson = new Table(myDatabase, "DimSalesperson");
//Column One: Salesperson Key
Column salespersonKey = new Column(DimSalesperson, "SalespersonKey", DataType.Int);
salespersonKey.Nullable = false;
DimSalesperson.Columns.Add(salespersonKey);
//Column Two: Salesperson Alt Key
Column salespersonAltKey = new Column(DimSalesperson, "SalespersonAltKey", DataType.NVarChar(10));
salespersonAltKey.Nullable = false;
DimSalesperson.Columns.Add(salespersonAltKey);
//Column Three: Salesperson Name
Column salespersonName = new Column(DimSalesperson, "SalespersonName", DataType.NVarChar(50));
salespersonName.Nullable = true;
DimSalesperson.Columns.Add(salespersonName);
//Column Four: Store Name
Column storeName = new Column(DimSalesperson, "StoreName", DataType.NVarChar(50));
storeName.Nullable = true;
DimSalesperson.Columns.Add(storeName);
//Column Five: Store Geography Key
Column storeGeographyKey = new Column(DimSalesperson, "StoreGeographyKey", DataType.Int);
storeGeographyKey.Nullable = true;
DimSalesperson.Columns.Add(storeGeographyKey);
//Primary Key
Index primaryKeyIndex3 = new Index(DimSalesperson, "PK_DimSalesperson");
primaryKeyIndex3.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex3.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex3, "salespersonKey"));
DimSalesperson.Indexes.Add(primaryKeyIndex3);
DimSalesperson.Create();
Console.WriteLine("DimSalesperson Table Created");
//Creating DimDate
Table DimDate = new Table(myDatabase, "DimDate");
//Column One: Date Key
Column dateKey = new Column(DimDate, "DateKey", DataType.Int);
dateKey.Nullable = false;
DimDate.Columns.Add(dateKey);
//Column Two: Calendar Year
Column calendarYear = new Column(DimDate, "CalendarYear", DataType.Int);
calendarYear.Nullable = false;
DimDate.Columns.Add(calendarYear);
//Column Three: Calendar Quarter
Column calendarQuarter = new Column(DimDate, "Calendar Quarter", DataType.Int);
calendarQuarter.Nullable = false;
DimDate.Columns.Add(calendarQuarter);
//Column Four: Month Of Year
Column monthOfYear = new Column(DimDate, "MonthOfYear", DataType.Int);
monthOfYear.Nullable = false;
DimDate.Columns.Add(monthOfYear);
//Column Five: Month Name
Column monthName = new Column(DimDate, "MonthName", DataType.NVarChar(15));
monthName.Nullable = false;
DimDate.Columns.Add(monthName);
//Column Six: Day Of Month
Column dayOfMonth = new Column(DimDate, "DayOfMonth", DataType.Int);
dayOfMonth.Nullable = false;
DimDate.Columns.Add(dayOfMonth);
//Column Seven: Day Of Week
Column dayOfWeek = new Column(DimDate, "DayOfWeek", DataType.Int);
dayOfWeek.Nullable = false;
DimDate.Columns.Add(dayOfWeek);
//Column Eight: Day Name
Column dayName = new Column(DimDate, "DayName", DataType.NVarChar(15));
dayName.Nullable = false;
DimDate.Columns.Add(dayName);
//Column Nine: Fiscal Year
Column fiscalYear = new Column(DimDate, "FiscalYear", DataType.Int);
fiscalYear.Nullable = false;
DimDate.Columns.Add(fiscalYear);
//Column Ten: Fiscal Quarter
Column fiscalQuarter = new Column(DimDate, "FiscalQuarter", DataType.Int);
fiscalQuarter.Nullable = false;
DimDate.Columns.Add(fiscalQuarter);
//Primary Key
Index primaryKeyIndex4 = new Index(DimDate, "PK_DimDate");
primaryKeyIndex4.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex4.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex4, "dateKey"));
DimDate.Indexes.Add(primaryKeyIndex4);
DimDate.Create();
Console.WriteLine("DimDate Table Created");
//Creating Fact Table
Table factTable = new Table(myDatabase, "Fact Table");
//Column One: Product Key
Column ProductKey = new Column(factTable, "ProductKey", DataType.Int);
ProductKey.Nullable = false;
factTable.Columns.Add(ProductKey);
//Column Two: Customer Key
Column CustomerKey = new Column(factTable, "CustomerKey", DataType.Int);
CustomerKey.Nullable = false;
factTable.Columns.Add(CustomerKey);
//Column Three: Sales Person Key
Column SalespersonKey = new Column(factTable, "SalespersonKey", DataType.Int);
SalespersonKey.Nullable = false;
factTable.Columns.Add(SalespersonKey);
//Column Four: Order Date Key
Column OrderDateKey = new Column(factTable, "OrderDateKey", DataType.Int);
OrderDateKey.Nullable = false;
factTable.Columns.Add(OrderDateKey);
//Column Five: Order Number
Column OrderNo = new Column(factTable, "OrderNo", DataType.Int);
OrderNo.Nullable = false;
factTable.Columns.Add(OrderNo);
//Column Six: Item Number
Column ItemNo = new Column(factTable, "ItemNo", DataType.Int);
ItemNo.Nullable = false;
factTable.Columns.Add(ItemNo);
//Column Seven: Quantity
Column Quantity = new Column(factTable, "Quantity", DataType.Int);
Quantity.Nullable = false;
factTable.Columns.Add(Quantity);
//Column Eight: Sales Amount
Column SalesAmount = new Column(factTable, "SalesAmount", DataType.Money);
SalesAmount.Nullable = false;
factTable.Columns.Add(SalesAmount);
//Column Nine: Cost
Column Cost = new Column(factTable, "Cost", DataType.Money);
Cost.Nullable = false;
factTable.Columns.Add(Cost);
factTable.Create();
Console.WriteLine("Fact Table Created");
Console.WriteLine("Database Created");
}
}
}
解决方法:
创建外键约束并添加到子表中
ForeignKeyConstraint fk = new ForeignKeyConstraint(
"ForeignKey", objCustomer.Columns["CustomerID"], objOrder.Columns["CustomerID"]);
objOrder.Constraints.Add(fk);
您可以使用以下链接作为参考.
Refer Link1
Refer Link2
内容总结
以上是互联网集市为您收集整理的如何在C#中为表设置外键？全部内容，希望文章能够帮你解决如何在C#中为表设置外键？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】