AcceptRejectRule [ForeignKeyConstraint only] |
AcceptRejectRule rule = Constraint.AcceptRejectRule; Constraint.AcceptRejectRule = rule; |
Determines what happens to child rows when the DataRow.AcceptChanges( ) method is called on a parent row (usually as part of a data source update). You can use any value from the AcceptRejectRule enumeration, as shown in Table 26-1. By default, this is None.
Value |
Description |
---|---|
Cascade |
When AcceptChanges( ) is called on a parent record, AcceptChanges( ) is also called on all linked child records. |
None |
No special action occurs when AcceptChanges( ) is called on a parent record. |
Using a value of Cascade may prevent child rows from being updated when you merge changes back into the data source. Thus, it's strongly recommended that you use the default of None, unless you don't intend to commit DataSet changes.
Columns |
DataColumn[] cols = Constraint.Columns; |
This returns an array with all the DataColumn objects for this Constraint. In the case of a UniqueConstraint, this is a column or combination of columns that must be unique in the DataTable. In the case of a ForeignKeyConstraint, this typically includes the linked column (or group of columns) from the child DataTable.
The following code snippet retrieves all the Constraint columns and displays their names:
foreach(DataColumn col in Constraint.Columns) { Console.WriteLine(col.ColumnName); }
ConstraintName |
string constraintName = Constraint.ConstraintName; Constraint.ConstraintName = constraintName; |
This is the name that identifies a Constraint. It's primarily used to retrieve or remove a Constraint object by name from the ConstraintCollection.
The following code snippet displays the ConstraintName for every Constraint in a DataTable:
foreach(Constraint c in dt.Constraints) { Console.WriteLine(c.ConstraintName); }
DeleteRule [ForeignKeyConstraint only] |
Rule rule = Constraint.DeleteRule; Constraint.DeleteRule = rule; |
Determines what happens to child rows when a parent is deleted. You can use any value from the Rule enumeration, as shown in Table 26-2. By default, this is Cascade, which means all child rows are deleted along with the parent.
Value |
Description |
---|---|
Cascade |
If the parent row is deleted, the child rows are also deleted. |
None |
No action is taken on child rows. Thus, if you try to delete a parent that has linked children, an exception is thrown. This is the traditional SQL Server behavior. |
SetDefault |
If the parent row is deleted, the child rows have the default value placed in their foreign key column, if allowed (otherwise an exception is thrown). |
SetNull |
If the parent row is deleted, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown. |
In this example, all the child order records are deleted when the customer parent record is deleted:
// This is the ForeignKeyConstraint for the Orders table. fkc.DeleteRule = Rule.Cascade; // Select the first row. DataRow row = ds.Tables["Customers"].Rows[0]; Console.WriteLine("Deleting: " + row["CustomerID"].ToString()); // Delete this customer (and any linked order records). row.Delete();
IsPrimaryKey [UniqueKeyConstraint only] |
bool isPrimaryKey = Constraint.IsPrimaryKey; Constraint.IsPrimaryKey = isPrimaryKey; |
Indicates whether the UniqueKeyConstraint represents the primary key for the table.
You can specify that a UniqueKeyConstraint should represent the primary key when creating the UniqueKeyConstraint by setting the IsPrimaryKey property to true. The following code snippet creates a UniqueKeyConstraint that represents a primary key defined on a first and last name column:
// Create an array with the two columns. DataColumn[] cols = new DataColumn[] {dt.Columns["LastName"], dt.Columns["FirstName"]}; // Create the UniqueConstraint object to represent the primary key. UniqueConstraint uc = new UniqueConstraint("FullName", cols, true); // Add the UniqueConstraint to the table. dt.Constraints.Add(uc);
A primary key is created automatically when you use the DataAdapter.FillSchema( ) method, as long as there is at least one unique column in the query. There can be only one primary key on a DataTable at a time. If you attempt to set a second primary key on a table, the original primary key is downgraded to a unique column.
RelatedColumns [ForeignKeyConstraint only] |
DataColumn[] cols = Constraint.RelatedColumns; |
This returns an array containing the parent DataColumn objects for this relationship. Typically, this is a single column from the parent DataTable. For example, if you define a Customers.CustomerID Orders.CustomerID relationship, the Orders table contains the ForeignKeyConstraint object, and the Customers table is the related table. The ForeignKeyConstraint.RelatedColumns property then returns a single DataColumn representing the Customers.CustomerID field.
The following code snippet retrieves the first parent DataColumn and displays its name:
DataColumn col = fkc.RelatedColumns[0]; Console.WriteLine("The linked column child is: " + col.ColumnName);
RelatedTable [ForeignKeyConstraint only] |
DataTable ds = Constraint.RelatedTable; |
Retrieves the parent DataTable object for this ForeignKeyConstraint. For example, in a Customers Orders relationship, the Orders table contains the ForeignKeyConstraint object, and the Customers table is the related table. This property is primarily included for convenience; you can also retrieve the table directly from the DataSet.
The following code retrieves the related DataTable and displays some basic information about it in a console window.
DataTable dt = fkc.RelatedTable; // Print the name and number of rows of the child table. Console.WriteLine(dt.TableName, dt.Rows.Count.ToString());
You can also retrieve a reference to the child table in which the constraint is applied using the Constraint.Table property.
Table |
DataTable ds = Constraint.Table; |
Retrieves the DataTable object that the Constraint belongs to.
The following code retrieves the containing DataTable and displays some basic information about it in a console window:
DataTable dt = dr.Table; // Print the name and number of rows of the child table. Console.WriteLine(dt.TableName, dt.Rows.Count.ToString());
In the case of a ForeignKeyConstraint, this is the child table in which the constraint is applied. The parent table can be retrieved through the ForeignKeyConstraint.RelatedTable property.
UpdateRule [ForeignKeyConstraint only] |
Rule rule = Constraint.UpdateRule; Constraint.UpdateRule = rule; |
Determines what happens if the parent's key column is modified. You can use any value from the Rule enumeration, as shown in Table 26-3. The default is Cascade, which means that the child rows are updated to point to the new value.
Value |
Description |
---|---|
Cascade |
If the linked column is changed in the parent, the foreign key column in all child rows is updated accordingly. |
None |
No action is taken on child rows. Thus, if you change the linked column of a parent that has children, an exception is thrown. This is the traditional SQL Server behavior. |
SetDefault |
If the linked column of a parent row is changed, the foreign key column in the child rows is reset to the default value, if allowed (otherwise an exception is thrown). |
SetNull |
If the linked column of the parent row is changed, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown. |
In this example, all the child order records are updated to use the new CustomerID value:
// This is the ForeignKeyConstraint for the Orders table. fkc.UpdateRule = Rule.Cascade; // Select the first row. DataRow row = ds.Tables["Customers"].Rows[0]; Console.WriteLine("Modifying: " + row["CustomerID"].ToString()); // Modify the CustomerID (all any linked order records will be updated). row["CustomerID"] = "NEWCUST";