MSDN Documentation

Data Constraints in ADO.NET

Data constraints are rules that enforce data integrity within a dataset. In ADO.NET, these constraints are represented by classes that derive from the abstract Constraint class. The primary types of constraints you'll encounter are UniqueConstraint and ForeignKeyConstraint.

UniqueConstraint

A UniqueConstraint ensures that the values in one or more columns of a DataTable are unique. This is analogous to a unique index or primary key constraint in a relational database.

Key Properties:

• Columns: A collection of DataColumn objects that constitute the unique key.
• IsPrimaryKey: A boolean value indicating whether this constraint is also the primary key for the table.

Example:

DataTable customersTable = new DataTable("Customers");
DataColumn idColumn = customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("Name", typeof(string));

// Set CustomerID as the primary key
customersTable.Constraints.Add(new UniqueConstraint(idColumn, true));

// Add another unique constraint on Name (if needed)
// customersTable.Constraints.Add(new UniqueConstraint(new DataColumn[] { customersTable.Columns["Name"] }));
            

ForeignKeyConstraint

A ForeignKeyConstraint enforces referential integrity between two tables. It specifies that values in the foreign key column(s) of a child table must exist in the unique (or primary key) column(s) of a parent table. This prevents "orphan" records.

Key Properties:

• RelatedTable: The parent table in the relationship.
• Columns: The foreign key columns in the child table.
• RelatedColumns: The unique/primary key columns in the parent table.
• AcceptRejectRule: Specifies how changes are handled when AcceptChanges() is called on a row with pending deletes or rejects.
• DeleteRule: Specifies the action to take when a row in the parent table is deleted.
• UpdateRule: Specifies the action to take when a value in a parent table's unique key column is updated.

Common Rule Actions:

• Rule.None: No action is taken.
• Rule.Cascade: The corresponding changes are propagated to the child table.
• Rule.SetNull: The foreign key columns in the child table are set to DBNull.Value.
• Rule.SetDefault: The foreign key columns in the child table are set to their default values.

Example:

DataTable ordersTable = new DataTable("Orders");
DataColumn orderIDColumn = ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));

// Assume customersTable and its UniqueConstraint for CustomerID already exist

// Add ForeignKeyConstraint
ForeignKeyConstraint fkConstraint = new ForeignKeyConstraint(
    customersTable.Columns["CustomerID"], // Parent column(s)
    ordersTable.Columns["CustomerID"]     // Child column(s)
);
fkConstraint.AcceptRejectRule = AcceptRejectRule.None;
fkConstraint.DeleteRule = Rule.Cascade; // If a customer is deleted, delete their orders
fkConstraint.UpdateRule = Rule.Cascade; // If a customer ID changes, update it in orders

ordersTable.Constraints.Add(fkConstraint);

// You would typically create a DataRelation to represent this relationship visually and programmatically.
DataRelation relation = new DataRelation("CustOrders",
    customersTable.Columns["CustomerID"],
    ordersTable.Columns["CustomerID"],
    false // This parameter controls whether to cascade deletes/updates; the ForeignKeyConstraint handles this more granularly.
);
// dataset.Relations.Add(relation); // If using a DataSet
            

Creating Constraints

Constraints are added to the Constraints collection of a DataTable. You can add them during table creation or later. It's generally recommended to define primary keys and foreign key relationships before populating the tables with data to ensure integrity from the start.

Benefits of Using Constraints

• Data Integrity: Enforces business rules and prevents invalid data.
• Referential Integrity: Ensures relationships between tables remain valid.
• Data Consistency: Maintains a high level of accuracy across your dataset.
• Simplified Data Management: ADO.NET can automatically manage cascading operations based on constraint rules.

"Constraints are fundamental to maintaining the quality and reliability of your data within an ADO.NET DataTable."

By effectively using UniqueConstraint and ForeignKeyConstraint, you can build more robust and trustworthy data-driven applications.