Recipe 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"]}, false));

// Set the multicolumn primary key.
result.Append("Creating primary key on parent table." +
    Environment.NewLine);
dtParent.PrimaryKey = new DataColumn[] {pCols["ParentKey1"],
    pCols["ParentKey2"]};

// Add the parent table to the DataSet.
ds.Tables.Add(dtParent);

// Create the child table.
result.Append("Creating child table." + Environment.NewLine);
DataTable dtChild = new DataTable("Child");
DataColumnCollection cCols = dtChild.Columns;
cCols.Add("ChildIndex1", typeof(Int32)).Unique = true;
cCols.Add("ParentKey1", typeof(Int32));
cCols.Add("ParentKey2", typeof(Int32));
cCols.Add("ChildData1", typeof(String));
cCols.Add("ChildData2", typeof(String));
ds.Tables.Add(dtChild);

// Set the foreign-key constraint.
result.Append("Creating foreign key contraint." + Environment.NewLine);
dtChild.Constraints.Add("FKConstraint",
    new DataColumn[] {pCols["ParentKey1"], pCols["ParentKey2"]},
    new DataColumn[] {cCols["ParentKey1"], cCols["ParentKey2"]});

// Create the relation between parent and child.
result.Append("Creating relationship." + Environment.NewLine);
ds.Relations.Add("Relation",
    new DataColumn[] {dtParent.Columns["ParentKey1"], dtParent.Columns["ParentKey2"]},
    new DataColumn[] {dtChild.Columns["ParentKey1"], dtChild.Columns["ParentKey2"]},
    true);

resultTextBox.Text = result.ToString( ) + Environment.NewLine + "Done.";

Discussion

Unique and foreign-key constraints

The System.Data.Constraint class is an abstract class that is the base class for the two .NET Framework built-in classes:

System.Data.UniqueConstraint

Ensures that all data in the specified column or columns in the row is unique within the table. Defining a primary key for a DataTable by setting the PrimaryKey property automatically creates a unique constraint for the specified column or columns.

System.Data.ForeignKeyConstraint

Enforces rules about how updates of values in rows or row deletions are propagated to related tables through the DeleteRule and UpdateRule properties, which define the action to be taken.

By default, a UniqueConstraint is created on the parent table and a ForeignKeyConstraint is created on the child table when a DataRelation object is created relating the two tables. Constraints are only enforced if the EnforceConstraints property is true.

The Add( ) method of the ConstraintCollection of the DataTable, which is accessed using the Constraints property, has two overloads that you can use to add a unique constraint to a table. The prototypes are:

public void Add(String name, DataColumn col, Boolean isPrimaryKey);
public void Add(String name, DataColumn[] cols, Boolean isPrimaryKey);

The arguments are:

name

The name of the unique constraint

col

The unique column in the table

cols

The array of unique data columns in the table

isPrimaryKey

Specifies whether the columns or columns represent the primary key for the table

There are two methods that you can use to add a foreign key constraint to a table. The prototypes are:

public void Add(String name, DataColumn parentCol, DataColumn childCol);
public void Add(String name, DataColumn[] parentCols,
    DataColumn childCols);

The arguments are:

name

The name of the foreign key constraint

parentCol

The column in the parent table

childCol

The column in the child table

parentCols

The array of columns in the parent table

childCols

The array of columns in the child table

There is also one method that you can use to add either a unique constraint or a foreign key constraint to a table. The prototype is:

public void Add(Constraint constraint);

The argument is:

constraint

The unique constraint or foreign key constraint object to add to the table

PrimaryKey

The PrimaryKey property of the DataTable is used to set or get the primary key for a table. The prototypes are:

DataColumn[] pka = DataTable.PrimaryKey;
DataTable.PrimaryKey = pka;

The argument is:

pka

An array of columns making up the primary key

DataRelation

The Add( ) method of the DataRelationCollection of the DataSet, which is accessed using the Relations property, has seven overloads that you can use to add a relationship between two tables to the DataSet. The prototypes are:

public void Add(DataRelation dr);
public virtual DataRelation Add(DataColumn parentCol, DataColumn childCol);
public virtual DataRelation Add(DataColumn[] parentCols,
    DataColumn[] childCols);
public virtual DataRelation Add(String name, DataColumn parentCol,
    DataColumn childCol);
public virtual DataRelation Add(String name, DataColumn[] parentCols,
    DataColumn[] childCols);
public virtual DataRelation Add(String name, DataColumn parentCol,
    DataColumn childCol, Boolean createFKConstraint);
public virtual DataRelation Add(String name, DataColumn[] parentCols,
    DataColumn[] childCols, Boolean createFKConstraint);

The arguments are:

dr

A data relationship between a parent and child table

parentCol

A column in the parent table

childCol

A column in the child table

parentCols

An array of columns in the parent table

childCols

An array of columns in the child table

name

The name of the data relation

createFKConstraint

Specifies whether to create a foreign key constraint