title: Can someone explain how to set up a foreign key for a table in C#?
body: <p>I am working on a project where I am creating tables within a  database in C# and I cannot figure out how to create a foreign key.</p>
  
  <p>Here is the SQL of what I am trying to create:</p>
  
  <pre><code>  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]
      )
    )
  </code></pre>
  
  <p>For example, I am simply trying to set up the "REFERENCES" part for ProductKey in how it references the ProductKey column from the DimProduct table.</p>
  
  <p>Here is the code I have for creating the ProductKey column in the factTable table:</p>
  
  <pre><code>    //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);
  </code></pre>
  
  <p>Here is the code I have for the DimProduct table I am trying to reference:</p>
  
  <pre><code>//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();
  </code></pre>
  
  <p>I did find this link: <a href="https://msdn.microsoft.com/en-US/library/ms162566.aspx" rel="nofollow">LINK1</a></p>
  
  <p>It gives the following example, but I cannot get it to work:</p>
  
  <pre><code>//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();
  </code></pre>
  
  <p><strong>Any help or insight would be much appreciated! Thank you!</strong></p>