ADO.NET Cookbook

6.8. Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns

Problem

You need to create a constraint, primary key, or a relationship between two tables in a DataSet using more than one column.

Solution

Use the System.Data.UniqueConstraint and System.Data.ForeignKeyConstraint types.

The sample code creates a DataSet containing two tables: Parent and Child. A multicolumn unique constraint and primary key are created for the Parent table. A multicolumn foreign-key constraint is created on the Child table. Finally, a DataRelation between the primary key in the Parent table and the foreign key in the Child table is created.

The C# code is shown in Example 6-26.

Example 6-26. File: MultiColumnConstraintAndRelationForm.cs

// Namespaces, variables, and constants using System; using System.Text; using System.Data; // . . . StringBuilder result = new StringBuilder( ); DataSet ds = new DataSet( ); // Create the parent table. result.Append("Creating parent table." + Environment.NewLine); DataTable dtParent = new DataTable("Parent"); DataColumnCollection pCols = dtParent.Columns; pCols.Add("ParentKey1", typeof(Int32)); pCols.Add("ParentKey2", typeof(Int32)); pCols.Add("ParentData1", typeof(String)); pCols.Add("ParentData2", typeof(String)); // Set the multicolumn unique constraint. result.Append("Creating unique constraint on parent table." + Environment.NewLine); dtParent.Constraints.Add(new UniqueConstraint("UConstraint", new DataColumn[] {pCols["ParentKey1"], pCols["ParentKey2"]}, ...