Chapter 8: Constraints and Data Relations


In chapter 3, you encountered constraints and data relations. In this chapter, we discuss constraints and data relations in more detail. First, we start this chapter with a discussion of constraints and how ADO.NET constraint objects maintain data integrity. Second, we discuss data relations. An object that encapsulates a data relationship may be a new concept for many programmers.

Understanding Constraints

A constraint is a rule used to automatically maintain the integrity of a column's data. When we say automatically, we mean that the database itself makes sure the rule is being followed when changes are made to the data. For example, a UNIQUE constraint on a column makes sure that there aren't any duplicate values in a column. There are different types of constraints, including NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints:

  • The NOT NULL constraint specifies that a column can't contain null values.

  • The UNIQUE constraint enforces a rule of uniqueness on a column, which means two rows of the column can't have duplicate values. A UNIQUE constraint may also apply to a combination of columns, which means the combination of the columns must be a unique value. For example, a database table can have two fields—a path and a file—and the combination of these fields must be unique to maintain the unique file entry in the database. In this case, the path or file column may have duplicate values individually, but they must be unique when used together.

  • A PRIMARY KEY constraint on a column or combination of columns enforces both the UNIQUE constraint and the NOT NULL constraint. This type of constraint is enforced when you need to identify column values as unique items and not null values such as a customer ID number or an order number.

  • The FOREIGN KEY constraint identifies the relationships between two tables. A foreign key in the first table points to a primary key in the second table. Foreign keys prevent actions that would ignore rows with foreign key values when there are no primary keys with that value. For example, the Orders table of a database may have a foreign key on the CustomerID column of the Customers table, which would prevent a user from entering an order that related to a nonexistent customer.

  • The CHECK constraint enforces data integrity by limiting a column's values. For example, you can specify a column type as an integer, which means only integer values can be placed in a column.

To implement many of these constraints, ADO.NET provides various classes. In the following sections, we discuss some of these classes, their members, and how to use them to implement constraints.

Implementing the Constraint Classes

The Constraint class represents a constraint and serves as a base class of the ForeignKeyConstraint and UniqueConstraint classes. This class provides three properties: ConstraintName represents the name of a constraint, ExtendedProperties represents a collection of user-defined properties, and Table represents a DataTable object to which the constraint applies. Before you learn how to implement constraints on a table, you'll look at the constraint classes.

Implementing the ForeignKeyConstraint Class

The ForeignKeyConstraint class creates a FOREIGN KEY constraint. In a primary key/foreign key relationship, first column acts as a parent column, and the second column acts as a child column. The ForeignKeyConstraint class has five overloaded constructors. Each of these constructors takes two DataColumn objects as a parent and a child column. Optionally, you can also specify the relation name.

The ForeignKeyConstraint class provides properties that can represent the relation name; the columns participating in the relationship; the accept, delete, and update rules; and so on. Table 8-1 describes the ForeignKeyConstraint class properties.

Table 8-1: The ForeignKeyConstraint Class Properties

PROPERTY

DESCRIPTION

AcceptRejectRule

Occurs when the AcceptChanges method of a DataTable is called. The parameter of AcceptRejectRule is represented by the AcceptRejectRule enumeration, which has two members: Cascade and None. The default action is None.

Columns

Returns the child columns of a constraint.

ConstraintName

Represents the name of the constraint.

DeleteRule

Occurs when a row is deleted. The parameter of DeleteRule is represented by the Rule enumeration, which has four members: Cascade, None, SetDefault, and SetNull. The default action is Cascade.

ExtendedProperties

Returns any user-defined properties that exist for the object.

RelatedColumns

Represents the parent column of a constraint.

Table

Returns the DataTable of a constraint.

UpdateRule

Occurs when a row is updated. The parameter of UpdateRule is represented by the Rule enumeration, which has four members: Cascade, None, SetDefault, and SetNull. The default action is Cascade.

Listing 8-1 creates a FOREIGN KEY constraint on the id column of custTable and the CustID column of ordersTable. custTable and ordersTable are two DataTable objects created by the CreateCustomersTable and CreateOrdersTable methods, respectively.

Listing 8-1: Creating a FOREIGN KEY Constraint

start example
 Dim custTable As DataTable = CreateCustomersTable() Dim ordersTable As DataTable = CreateOrdersTable() Dim parentColumn As DataColumn = custTable.Columns("id") Dim childColumn As DataColumn = ordersTable.Columns("CustID") Dim fkConst As ForeignKeyConstraint = New ForeignKeyConstraint _ "CustOrderConts", parentColumn, childColumn) fkConst.DeleteRule = Rule.SetNull fkConst.UpdateRule = Rule.Cascade fkConst.AcceptRejectRule = AcceptRejectRule.Cascade 
end example

Caution

Be careful when using the Cascade action. The default action for AcceptRejectRule is None, but the default action for UpdateRule and DeleteRule is Cascade. Calling the Cascade action on DeleteRule deletes all the children rows, which are related using the FOREIGN KEY constraint.

Using the UniqueConstraint Class

The UniqueConstraint object represents a UNIQUE constraint, which ensures that the value of a column is unique. Table 8-2 describes the UniqueConstraint class properties.

Table 8-2: The UniqueConstraint Class Properties

PROPERTY

DESCRIPTION

Columns

Returns an array of columns that this constraint affects

ConstraintName

Represents the name of the constraint

ExtendedProperties

Returns a collection of any user-defined properties that exist for the object

IsPrimaryKey

Represents whether the constraint is a primary key

Table

Returns the DataTable of a constraint

Listing 8-2 creates two UNIQUE constraints on the id column of custTable and the OrderId column of ordersTable.

Listing 8-2: Creating a UNIQUE Constraint

start example
 ' Create unique constraints Dim idCol As DataColumn = New DataColumn() idCol = custTable.Columns("id") Dim unqConst1 As UniqueConstraint = _ New UniqueConstraint("idUnqConst1", idCol) idCol = ordersTable.Columns("OrderId") Dim unqConst2 As UniqueConstraint = _ New UniqueConstraint("idUnqConst2", idCol) 
end example

Note

You can also set the Unique property of a DataColumn to True to make a column unique. Chapter 3 discusses this technique.

Using the ConstraintCollection Class

A DataTable can have multiple constraints. The ConstraintCollection class represents a collection of constraints of a DataTable and is accessed through the Constraints property of a DataTable. This class provides two properties: List and Item. The List property returns a list of all the constraints in the collection in an ArrayList object. The Item property is the indexer for the ConstraintCollection class, which returns a constraint at a specified index.

Similar to other collection classes, the ConstraintCollection class also provides methods to add and remove items to the collection. Table 8-3 describes the ConstraintCollection class methods.

Table 8-3: The ConstraintCollection Class Methods

METHOD

DESCRIPTION

Add

This method adds a constraint to the collection. (The following section describes in this method in more detail.)

AddRange

Adds a collection of constraints to the existing collection.

CanRemove

Checks whether a constraint can be removed from the collection.

Clear

Removes all constraints from a collection.

Contains

Indicates whether a constraint exists in the collection. Returns True if a constraint exists; otherwise, returns False. This is useful when you need to remove or read a constraint from the collection.

IndexOf

Returns the index of a constraint in the collection. You can use the constraint name or an object.

OnCollectionChanged

An event raises when the constraint collection is changed by adding or removing constraints.

Remove

Deletes a constraint from the collection. You can specify the constraint name or an object.

RemoveAt

Removes a constraint at the specified index in the collection.

You've now seen the constraint classes and their members. Next let's put these classes together and see how to use them in applications.

Adding and Removing Constraints

You use the Add method of the ConstraintCollection to add new constraints to the collection of constraints associated with a DataTable. Listing 8-3 adds a FOREIGN KEY constraint to a collection and removes the first constraint. As you can see from the code, the Contains method returns True if the collection contains a constraint, and the CanRemove method returns True if the program can delete the constraint. The Remove method removes the constraint. You can also use the RemoveAt method if you want to delete a constraint based on the index.

Listing 8-3: Adding and Removing Constraints

start example
 ' Create two DataTable objects ' Customer and Orders Dim custTable As DataTable = CreateCustomersTable() Dim ordersTable As DataTable = CreateOrdersTable() Try       ' Add a foriegn key constraint       Dim parentColumn As DataColumn = custTable.Columns("id")       Dim childColumn As DataColumn = ordersTable.Columns("CustID")       Dim fkConst As ForeignKeyConstraint = New ForeignKeyConstraint _    ("CustOrderConts", parentColumn, childColumn)       fkConst.DeleteRule = Rule.SetNull       fkConst.UpdateRule = Rule.Cascade       fkConst.AcceptRejectRule = AcceptRejectRule.Cascade       ordersTable.Constraints.Add(fkConst)  Catch exp As Exception       MessageBox.Show(exp.Message) End Try ' Create a DataSet and add DataTables to it Dim ds As DataSet = New DataSet("CustOrderDataSet") ' Add DataTables to a DataSet ds.Tables.Add(custTable) ds.Tables.Add(ordersTable) ' Enforce Constraints ds.EnforceConstraints = True ' Using Contains, CanRemove and Remove methods ' Get the first constraint Dim cnst1 As Constraint = ordersTable.Constraints(0) Try       If ordersTable.Constraints.Contains(cnst1.ConstraintName) Then         If ordersTable.Constraints.CanRemove(cnst1) Then           ordersTable.Constraints.Remove(cnst1)         End If       End If Catch myException As Exception       Console.WriteLine(myException.Message) End Try 
end example

Note

You must set the DataSet property EnforceConstraints to True if you want to save the constraint in a DataSet.

Note

The CreateCustomersTable and CreateOrdersTable methods return two DataTable objects created programmatically. If you're using constraints in a connected environment, you can get these DataTable objects from a DataSet using DataSet.Tables("TableName") or DataSet.Tables(index).

Listing 8-4 adds UNIQUE constraints to two tables.

Listing 8-4: Adding UNIQUE Constraints

start example
 ' Create unique constraints Dim idCol As DataColumn = New DataColumn() idCol = custTable.Columns("id") Dim unqConst1 As UniqueConstraint = _ New UniqueConstraint("idUnqConst1", idCol) idCol = ordersTable.Columns("OrderId") Dim unqConst2 As UniqueConstraint = _ New UniqueConstraint("idUnqConst2", idCol) ' Add constraints to DataTables custTable.Constraints.Add(unqConst1) ordersTable.Constraints.Add(unqConst2) 
end example

Reading All the Constraints

Reading all the constraints is pretty simple. You just read all the items in the collection. Listing 8-5 reads all the constraints of a collection.

Listing 8-5: Reading All the Constraints of a Collection

start example
 Dim contCollection As ConstraintCollection = _       ordersTable.Constraints     Dim str As String = "Number of Constraints:" & _      contCollection.Count.ToString()     Dim i As Integer     Dim cnst As Constraint     For Each cnst In contCollection       str = str + ", "       str = str + cnst.ConstraintName     Next 
end example

Adding PRIMARY KEY Constraints

There's no specific primary key class to add a PRIMARY KEY constraint to a DataTable. The PrimaryKey property of DataTable sets the primary key of that table, and you assign a DataColumn as the PrimaryKey property. Listing 8-6 creates a DataColumnCollection and sets it as the primary key of the CustTable.

Listing 8-6: Adding a PRIMARY KEY Constaint to a DataTable

start example
 Dim custTable As DataTable = CreateCustomersTable() ' Make the ID column the primary key column. Dim PrimaryKeyColumns() As DataColumn = New DataColumn(1) {} PrimaryKeyColumns(0) = custTable.Columns("id") custTable.PrimaryKey = PrimaryKeyColumns 
end example

Using SQL Statements to Add and Remove Constraints

When working in a connected environment, using SQL may be a better way to add constraints to a database table. You can add constraints to a table when you create a table or by altering an already existing table.

The CREATE TABLE SQL statement allows you to set a column as a constraint when you create a table. The CONSTRAINT keyword sets a column as the specified constraint. For example, the following SQL statement creates a table with myId as the primary key:

 Dim sql As String = "CREATE TABLE myTable" & _  "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," & _  "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)" 

You can always add and delete constraints from a database table using the ALTER TABLE SQL statement. If you read the SQL documentation, you'll see that this statement allows options such as ADD CONSTRAINT and DROP CONSTRAINT to add and remove constraints.

If you're working in a connected environment and you can execute commands (through SqlCommand), using SQL statements to add and remove constraints is preferable because it entails less overhead and code and has better performance.




Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net