7.3 Constraints

As mentioned earlier, schema for a table is defined by the columns in the table and the constraints on those columns. There are two types of constraints that can be placed on a table. Unique constraints define a column or group of columns for which the value in the column or columns must be unique in each data row. Foreign key constraints define and restrict the action performed when a value in a column or columns is updated or deleted. Constraints belonging to the DataTable are stored as either UniqueConstraint or ForeignKeyConstraint objects in a ConstraintCollection object and are accessed through the Constraints property of the DataTable. This section examines some methods and properties of the ConstraintCollection.

To add a constraint to a table, the Add( ) method takes an argument specifying a reference to an existing constraint or takes specific arguments if a unique or foreign-key constraint is added. The following example demonstrates adding both a unique and foreign-key constraint by specifying a reference to an existing constraint:

// add a unique constraint by reference
UniqueConstraint uc = 
  new UniqueConstraint(dt.Columns["MyColumn"]);
dt.Constraints.Add(uc);

// add a foreign key constraint by reference (wxh - test)
ForeignKeyConstraint fc = new ForeignKeyConstraint(
    dtParent.Columns["ParentColumn"],
    dtChild.Columns["ChildColumn"]);
dt.Constraints.Add(fc);

Two overloads of the Add( ) method create and add UniqueConstraint objects in one statement. The methods take a constraint name, a reference either to a DataColumn or a DataColumn array, and an argument indicating whether the column or columns are a primary key.

// add a unique constraint that is also a primary key
dt.Constraints.Add("MyUniqueConstraint", dt.Columns["MyColumn"], true);

The other two overloads of the Add( ) method create and add a ForeignKeyConstraint in one statement. The methods take a constraint name and either two DataColumn references or two DataColumn arrays, as shown in the following example:

// add a foreign key constraint based on two columns
dt.Constraints.Add("MyForeignKeyConstraint",
    new DataColumn(dtParent.Columns["ParentCol1"],
    new dtParent.Columns["parentCol2"]),
    new DataColumn(dtChild.Columns["ChildCol1"],
    dtChild.Columns["ChildCol2"]));

There are several properties and methods that interrogate the collection of constraints within a table. The Count property returns the number of constraints in a table:

Int32 constraintCount = dt.Constraints.Count;

The Contains( ) method returns a value indicating whether a column with a specified name exists in the collection. The method takes an argument containing the column name:

Boolean colExists = dt.Columns.Exists("MyColumn");

The Remove( ), RemoveAt( ), and Clear( ) methods remove constraints from the DataTable. The Remove( ) method takes an argument that specifies either a constraint name or a reference to the constraint to be removed, as shown in the following example:

// remove a constraint by specifying the name of the constraint
dt.Constraints.Remove("MyConstraint");

// remove a constraint by specifying a reference to the constraint
DataConstraint constraint = new DataConstraint("MyConstraint");
dt.Constraints.Add(constraint);

// ... do some work

dt.Constraints.Remove(constraint);

The RemoveAt( ) method removes a constraint with a specified index from the collection, as shown in this example:

// remove the first constraint from the collection
dt.Constraints.RemoveAt(0);

The Clear( ) method removes all constraints from the constraint collection:

dt.Constraints.Clear();

Constraints are discussed in detail in Chapter 10.