How can I set a foreign key for a table in C #?
I am working on a program that creates a database and builds dimension and fact tables. I am having trouble setting up foreign keys and constraints.
Here is the actual SQL for what I am trying to do:
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]
)
)
Here is a fact table:
//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();
Here is all the C # code (NOTE: the fact table is at the bottom):
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");
}
}
}
+3
source to share
1 answer
Create foreign key constraint and add to child table
ForeignKeyConstraint fk = new ForeignKeyConstraint(
"ForeignKey", objCustomer.Columns["CustomerID"], objOrder.Columns["CustomerID"]);
objOrder.Constraints.Add(fk);
You can use the following links for your links.
Refer to Link1
Refer to Link2
+3
source to share