As we all know Dataset plays a significant role while working with .net applications. Those who are new to dataset, it is just an in memory representation of table data. This object is sufficient to differentiate classic ADO and ADO.NET.

Constraints are nothing but conditions applied to the table Column or Columns to maintain data integrity in the table. Generally we use constraints in the database tables. We can also implement Constraints in the dataset tables.  

 There are mainly 3 types of constraints in Datasets.

1. Unique Key Constraint
2. Primary  Key Constraint
3. Foreign Key Constraint

Before going in depth for constraint let’s create a dataset with some tables in VB.Net

Dim SqlConn As SqlConnection = New  SqlConnection(“ConnString”)
Dim SqlDa As SqlDataAdapter
Dim SqlDs As DataSet = New DataSet()

SqlDa = New SqlDataAdapter(“SELECT * FROM CustomerTable”, SqlConn)
SqlDa.Fill(SqlDs, “CustomerTable”)

SqlDa = New SqlDataAdapter(“SELECT * FROM CustomerOrderTable”, SqlConn)
SqlDa.Fill(SqlDs, “OrderTable”)

Now we are having a dataset named SqlDs with 2 tables named CustomerTable and OrderTable. Let’s apply different constraints to these tables.

1. UniqueKeyConstraint
A UniqueKey in a datatable defines the uniqueness of records means the data present in the specified column is unique. Unique constraint can be added to a data table by using the UniqueConstraint constructor. Please look at the below codes. The code creates a unique constraint on the CustomerID table of the CustomerTable. 

Dim AppTable As DataTable = SqlDs.Tables(“CustomerTable”)
Dim UniqConst As UniqueConstraint = New UniqueConstraint(“UniqueCustomer”, _
AppTable.Columns(“CustomerID”))
AppTable.Constraints.Add(UniqConst)

The Column having Unique constraint is allowed to have null values. So we can also create unique constraint as

SqlDs.Tables(“CustomerTable”).Columns(“CustomerID”).Unique = True            SqlDs.Tables(“CustomerTable”).Columns(“CustomerID”).AllowDBNull = True

If AllowDBNull is True then null value is allowed.

2. Primary Key Constraint
While we mark a column of a datatable as primary key, unique key is automatically created on the table and the AllowDbNull property becomes false. So the column having a primary key constraint must contain unique values and null value is not at all allowed. The code below creates a primary key on the CoustomerTable.CustomerID.

SqlDs.Tables(“CustomerTable”).PrimaryKey = New DataColumn(){SqlDs.Tables(“CustomerTable”).Columns(“CustomerID”)}

3. Foreign Key Constraint
A foreign Key on one table refers to the primary key of some other table. So if we are updating or deleting a row of one table having a PrimaryKeyConstraint on its column, ForeignKeyConstraint will then take decision to make changes on the other related tables. We need to set different values for different actions on Delete and Update. The rules and respective meanings are shown below.

Cascade: Delete or update related rows. This is the default.
None: No action taken on related rows.
SetDefault: Set values in related rows to the value contained in the System.Data.DataColumn.DefaultValue property.
SetNull:  Set values in related rows to DBNull.

The code here creates a ForeignKeyConstraint on CustomerOrder.CustomerKey for CustomerTable.CustomerID as PrimeryKey.

Dim FornKey As ForeignKeyConstraint
FornKey = New ForeignKeyConstraint(“ForKeyCustOrder”, SqlDs.Tables(“CustomerTable”).Columns(“CustomerID”), _
SqlDs.Tables(“CustomerOrder”).Columns(“CustomerKey”)) 

FornKey.DeleteRule = Rule.Cascade
FornKey.UpdateRule = Rule.Cascade

SqlDs.Tables(“CustomerOrder”).Constraints.Add(FornKey)
SqlDs.EnforceConstraints = True

The property EnforceConstraints specifies the  constraint should be enforced or not.