In Part 1 we examined the basics of DataSets and DataTables,
and how to specify relations among DataTables as well as add unique constraints to the columns in a DataTable.
In this part we'll continue our investigation into imposing constraints on DataSet DataTables.
Creating AutoIncrement Columns
The AutoIncrement property of the column may be set to True to ensure that the values in
that column are unique so that the column values to increment automatically when new rows are added to
the table. The other associated properties of an AutoIncrement column are
AutoIncrementSeed, AutoIncrementStep and ReadOnly.
AutoIncrementSeed is the starting number of increment and
AutoIncrementStep is the amount the auto-increment column is increased when each new record
is added. (Typically these values are set to 1 and 1.)
The following example creates a column that starts with a value of 1,000 and is automatically
incremented by one step when a new row is inserted to the DataTable.
Dim myTable As DataTable = New DataTable("EmployeeTable")
' Create a DataColumn and set various properties.
Dim myColumn As DataColumn = New DataColumn("EmployeeId")
myColumn.DataType = System.Type.GetType("System.Int32")
myColumn.ReadOnly = True
myColumn.AutoIncrement = True
myColumn.AutoIncrementSeed = 1000
myColumn.AutoIncrementStep = 1
' Add the column to the table.
myTable.Columns.Add(myColumn)
' Let us Add 10 rows to the table.
Dim myRow As DataRow
Dim intX As Integer
For intX = 0 To 9
myRow = myTable.NewRow()
myTable.Rows.Add(myRow)
Next intX
Creating a Primary Key Column for a Table
When a DataColumn is created as the PrimaryKey for a DataTable,
the table automatically sets the Unique property to True and the AllowDBNull
property of the column to False. Alternatively, if you remove a column from the PrimaryKey
property of a DataTable, the UniqueConstraint is removed.
The following example defines a single column as the primary key:
CustomerTable.PrimaryKey = New DataColumn() {CustomerTable.Columns("CustID")}
The following example defines two columns as a primary key:
CustomerTable.PrimaryKey = _
New DataColumn() { CustomerTable.Columns("CustLName"), _
CustomerTable.Columns("CustFName")}
' ... Or you can use ...
Dim PrimKey(2) As DataColumn
PrimKey(0) = CustomerTable.Columns("CustLName")
PrimKey(1) = CustomerTable.Columns("CustFName")
CustomerTable.PrimaryKey = PrimKey
Foreign Key Constraint
Generally, a ForeignKeyConstraint is used to impose rule for updates and deletes in
related tables. For example, if a particular 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
take decision what are the changes to be made in the related tables. That is, in order to
take different actions on Delete and Update, the DeleteRule and
UpdateRule properties of the ForeignKeyConstraints are set with different
values as follows:
Rule
Description
Cascade
Updates or Deletes related rows. [default]
None
Specifies that no action be taken on related rows.
SetDefault
Sets values in related rows to the default value.
SetNull
Sets values in related rows to DBNull
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"))
' Don't allow a customer with existing orders be deleted.
custOrderFK.DeleteRule = Rule.None
custDS.Tables("OrdersTable").Constraints.Add(custOrderFK)
AcceptRejectRule
If you are well versed with the DataSet, DataTable and DataRow objects then you are
probably aware of the AcceptChanges and RejectChanges methods that are common to
all three of these objects. 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 is to be taken on the child rows when AcceptChanges or
RejectChanges is called on the parent row.
The following table shows the list of the values to which the AcceptRejectRule can be set.
AcceptRejectRule
Description
Cascade
Accepts or rejects changes to child rows. [default]
None
Specifies that no action be taken on child rows.
The following line of code shows how to set the value for AcceptRejectRule property of
the ForeignKeyConstraint:
Now that we've looked at how to add the various types of constraints to a DataSet, you may be wondering
if there is an easier way to apply such constraints to a DataSet other than explicitly specifying all
constraints (as we've done thus far). The answer to that question is, Yes. There exists a FillSchema
method which will populate a DataSet's schema with the schema information of an underlying database
table. We will examine this method, and how to use it, in Part 3.
