Adding Constraints to a Table

You can use Constraints to enforce restrictions on the data in a DataTable, in order to maintain the integrity of the data. A constraint is an automatic rule, applied to a column or related columns, that determines the course of action when the value of a row is somehow altered. Constraints are enforced when the EnforceConstraints property of the DataSet is true.

There are two kinds of constraints in ADO.NET: the ForeignKeyConstraint, and the UniqueConstraint. By default, both constraints are created automatically when you create a relationship between two or more tables by adding a DataRelation to the DataSet. However, you can disable this behavior by specifying createConstraints = false when creating the relation.

ForeignKeyConstraint

A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint will determine what happens in the related tables.

The DeleteRule and UpdateRule properties of the ForeignKeyConstraint define the action to be taken when the user attempts to delete or update a row in a related table. The following table describes the different settings available for the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.

A ForeignKeyConstraint can restrict, as well as propagate, changes to related columns. Depending on the properties set for the ForeignKeyConstraint of a column, and if the EnforceConstraints property of the DataSet is true, performing certain operations on the parent row will result in an exception. For example, if the DeleteRule property of the ForeignKeyConstraint is None, a parent row cannot be deleted if it has any child rows.

You can create a foreign key constraint between single columns or between an array of columns by using the ForeignKeyConstraint constructor. Pass the resulting ForeignKeyConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a ForeignKeyConstraint.

When creating a ForeignKeyConstraint, you can pass the DeleteRule and UpdateRule values to the constructor as arguments, or you can set them as properties as in the following example (where the UpdateRule value is set to the default, Cascade).

Dim custOrderFK As ForeignKeyConstraint = New ForeignKeyConstraint("CustOrderFK", _
                                            custDS.Tables("CustTable").Columns("CustomerID"), _
                                            custDS.Tables("OrdersTable").Columns("CustomerID"))
custOrderFK.DeleteRule = Rule.None  
' Cannot delete a customer value that has associated existing orders.
custDS.Tables("OrdersTable").Constraints.Add(custOrderFK)
[C#]
ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",
                                     custDS.Tables["CustTable"].Columns["CustomerID"], 
                                     custDS.Tables["OrdersTable"].Columns["CustomerID"]);
custOrderFK.DeleteRule = Rule.None;  
// Cannot delete a customer value that has associated existing orders.
custDS.Tables["OrdersTable"].Constraints.Add(custOrderFK);

AcceptRejectRule

Changes to rows can be accepted using the AcceptChanges method or canceled using the RejectChanges method of the DataSet, DataTable, or DataRow. When a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods causes the AcceptRejectRule to be enforced. The AcceptRejectRule property of the ForeignKeyConstraint determines which action will be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.

The following table lists the values to which the AcceptRejectRule can be set.

UniqueConstraint

The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor. Pass the resulting UniqueConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a UniqueConstraint. When creating a UniqueConstraint for a column or columns, you can optionally specify whether the column or columns are a primary key.

You can also create a unique constraint for a column by setting the Unique property of the column to true. Alternatively, setting the Unique property of a single column to false removes any unique constraint that may exist. Defining a column or columns as the primary key for a table will automatically create a unique constraint for the specified column or columns. If you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed.

The following example creates a UniqueConstraint for two columns of a DataTable.

Dim custTable As DataTable = custDS.Tables("Customers")
Dim custUC As UniqueConstraint = New UniqueConstraint(New DataColumn() _
                              {custTable.Columns("CustomerID"), _
                               custTable.Columns("CompanyName")})
custDS.Tables("Customers").Constraints.Add(custUC)
[C#]
DataTable custTable = custDS.Tables["Customers"];
UniqueConstraint custUC = new UniqueConstraint(new DataColumn[] 
                              {custTable.Columns["CustomerID"], 
                               custTable.Columns["CompanyName"]});
custDS.Tables["Customers"].Constraints.Add(custUC);

See Also

Creating and Using DataSets | DataRelation Class | DataTable Class | ForeignKeyConstraint Class | UniqueConstraint Class