数据表约束DataTable Constraints

为了维护数据的完整性,可以使用约束来对 DataTable 中的数据施加限制。You can use constraints to enforce restrictions on the data in a DataTable, in order to maintain the integrity of the data. 约束是应用于某列或相关各列的自动规则,它决定了某行的值以某种方式更改时的操作过程。A constraint is an automatic rule, applied to a column or related columns, that determines the course of action when the value of a row is somehow altered. System.Data.DataSet.EnforceConstraints 的属性 DataSettrue时,将强制实施约束。Constraints are enforced when the System.Data.DataSet.EnforceConstraints property of the DataSet is true. 有关显示如何设置 EnforceConstraints 属性的代码示例,请参见 EnforceConstraints 参考主题。For a code example that shows how to set the EnforceConstraints property, see the EnforceConstraints reference topic.

ADO.NET 中有两种约束:ForeignKeyConstraintUniqueConstraintThere are two kinds of constraints in ADO.NET: the ForeignKeyConstraint and the UniqueConstraint. 默认情况下,通过将添加 DataRelation数据集,可以在创建两个或多个表之间创建关系时自动创建这两个约束。By default, both constraints are created automatically when you create a relationship between two or more tables by adding a DataRelation to the DataSet. 不过,您可以createConstraints = 在创建关系时指定 createConstraintsfalse来禁用此行为。However, you can disable this behavior by specifying createConstraints = false when creating the relation.

ForeignKeyConstraintForeignKeyConstraint

ForeignKeyConstraint强制执行有关如何传播相关表的更新和删除的规则。A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. 例如,如果更新或删除了一个表的行中的值,并且在一个或多个相关表中也使用相同的值,则 ForeignKeyConstraint 将确定相关表中发生的情况。For example, if a 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 determines what happens in the related tables.

DeleteRuleForeignKeyConstraint 的和 UpdateRule 属性定义ForeignKeyConstraint用户尝试删除或更新相关表中的行时要执行的操作。The DeleteRule and UpdateRule properties of the ForeignKeyConstraint define the action to be taken when the user attempts to delete or update a row in a related table. 下表描述了可用于ForeignKeyConstraintDeleteRuleUpdateRule属性的不同设置。The following table describes the different settings available for the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.

规则设置Rule setting 描述Description
CascadeCascade 删除或更新相关的行。Delete or update related rows.
SetNullSetNull 将相关行中的值设置为 DBNullSet values in related rows to DBNull.
SetDefaultSetDefault 将相关行中的值设置为默认值。Set values in related rows to the default value.
None 对相关行不执行任何操作。Take no action on related rows. 这是默认设置。This is the default.

ForeignKeyConstraint可以限制并传播对相关列的更改。A ForeignKeyConstraint can restrict, as well as propagate, changes to related columns. 根据为列的ForeignKeyConstraint设置的属性,如果DataSetEnforceConstraints属性为true,则对父行执行某些操作将导致异常。Depending on the properties set for the ForeignKeyConstraint of a column, if the EnforceConstraints property of the DataSet is true, performing certain operations on the parent row will result in an exception. 例如,如果ForeignKeyConstraintDeleteRule属性为None,则如果父行具有任何子行,则不能将其删除。For example, if the DeleteRule property of the ForeignKeyConstraint is None, a parent row cannot be deleted if it has any child rows.

您可以使用 ForeignKeyConstraint 构造函数在单个列之间或列的数组之间创建外键约束。You can create a foreign key constraint between single columns or between an array of columns by using the ForeignKeyConstraint constructor. 将生成的ForeignKeyConstraint对象传递给表的约束属性的Add方法,该属性是一个ConstraintCollectionPass the resulting ForeignKeyConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. 还可以将构造函数参数传递给ConstraintCollectionAdd方法的几个重载,以创建ForeignKeyConstraintYou can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a ForeignKeyConstraint.

当创建 ForeignKeyConstraint时,可以将 DeleteRuleUpdateRule 值作为参数传递给构造函数,也可以将它们设置为属性,如以下示例所示 (其中 DeleteRule 值设置为 None) 。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 DeleteRule value is set to None).

Dim custOrderFK As ForeignKeyConstraint = New ForeignKeyConstraint("CustOrderFK", _  
  custDS.Tables("CustTable").Columns("CustomerID"), _  
  custDS.Tables("OrdersTable").Columns("CustomerID"))  
custOrderFK.DeleteRule = Rule.None
' Cannot delete a customer value that has associated existing orders.  
custDS.Tables("OrdersTable").Constraints.Add(custOrderFK)  
ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",  
  custDS.Tables["CustTable"].Columns["CustomerID"],
  custDS.Tables["OrdersTable"].Columns["CustomerID"]);  
custOrderFK.DeleteRule = Rule.None;
// Cannot delete a customer value that has associated existing orders.  
custDS.Tables["OrdersTable"].Constraints.Add(custOrderFK);  

AcceptRejectRuleAcceptRejectRule

可以使用AcceptChanges方法接受对行的更改,或使用DataSetDataTableDataRowRejectChanges方法取消对行的更改。Changes to rows can be accepted using the AcceptChanges method or canceled using the RejectChanges method of the DataSet, DataTable, or DataRow. 数据集 包含 ForeignKeyConstraints时,调用 AcceptChangesRejectChanges 方法会强制执行 AcceptRejectRuleWhen a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods enforces the AcceptRejectRule. ForeignKeyConstraintAcceptRejectRule属性决定了对父行调用AcceptChangesRejectChanges时要对子行执行的操作。The AcceptRejectRule property of the ForeignKeyConstraint determines which action will be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.

下表列出了 AcceptRejectRule的可用设置。The following table lists the available settings for the AcceptRejectRule.

规则设置Rule setting 描述Description
CascadeCascade 接受或拒绝对子行的更改。Accept or reject changes to child rows.
None 对子行不执行任何操作。Take no action on child rows. 这是默认设置。This is the default.

示例Example

下面的示例创建一个 ForeignKeyConstraint,设置它的一些属性(包括 AcceptRejectRule),并将它添加到 ConstraintCollection 对象的 DataTableThe following example creates a ForeignKeyConstraint, sets several of its properties, including the AcceptRejectRule, and adds it to the ConstraintCollection of a DataTable object.

private void CreateConstraint(DataSet dataSet,
    string table1, string table2,string column1, string column2)
{
   // Declare parent column and child column variables.
   DataColumn parentColumn;
   DataColumn childColumn;
   ForeignKeyConstraint foreignKeyConstraint;

   // Set parent and child column variables.
   parentColumn = dataSet.Tables[table1].Columns[column1];
   childColumn = dataSet.Tables[table2].Columns[column2];
   foreignKeyConstraint = new ForeignKeyConstraint
      ("SupplierForeignKeyConstraint",  parentColumn, childColumn);

   // Set null values when a value is deleted.
   foreignKeyConstraint.DeleteRule = Rule.SetNull;
   foreignKeyConstraint.UpdateRule = Rule.Cascade;
   foreignKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;

   // Add the constraint, and set EnforceConstraints to true.
   dataSet.Tables[table1].Constraints.Add(foreignKeyConstraint);
   dataSet.EnforceConstraints = true;
}
Private Sub CreateConstraint(dataSet As DataSet, _
   table1 As String, table2 As String, _
   column1 As String, column2 As String)

    ' Declare parent column and child column variables.
    Dim parentColumn As DataColumn
    Dim childColumn As DataColumn
    Dim foreignKeyConstraint As ForeignKeyConstraint

    ' Set parent and child column variables.
    parentColumn = dataSet.Tables(table1).Columns(column1)
    childColumn = dataSet.Tables(table2).Columns(column2)
    foreignKeyConstraint = New ForeignKeyConstraint _
       ("SupplierForeignKeyConstraint", parentColumn, childColumn)

    ' Set null values when a value is deleted.
    foreignKeyConstraint.DeleteRule = Rule.SetNull
    foreignKeyConstraint.UpdateRule = Rule.Cascade
    foreignKeyConstraint.AcceptRejectRule = AcceptRejectRule.None

    ' Add the constraint, and set EnforceConstraints to true.
    dataSet.Tables(table1).Constraints.Add(foreignKeyConstraint)
    dataSet.EnforceConstraints = True
End Sub

UniqueConstraintUniqueConstraint

UniqueConstraint对象(可分配给DataTable中的一列或一组列)确保指定的一列或多列中的所有数据对于每行都是唯一的。The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. 您可以使用 UniqueConstraint 构造函数创建列或列数组的唯一约束。You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor. 将生成的UniqueConstraint对象传递给表的约束属性的Add方法,该属性是一个ConstraintCollectionPass the resulting UniqueConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. 还可以将构造函数参数传递给ConstraintCollectionAdd方法的几个重载,以创建UniqueConstraintYou can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a UniqueConstraint. 为一列或多列创建 UniqueConstraint 时,可以选择指定列或列是否为主键。When creating a UniqueConstraint for a column or columns, you can optionally specify whether the column or columns are a primary key.

还可以通过将列的 unique 属性设置为 true,为列创建 unique 约束。You can also create a unique constraint for a column by setting the Unique property of the column to true. 或者,将单个列的 unique 属性设置为 false 将删除任何可能存在的唯一约束。Alternatively, setting the Unique property of a single column to false removes any unique constraint that may exist. 如果将一列或多列定义为表的主键,会自动为一个或多个指定的列创建唯一的约束。Defining a column or columns as the primary key for a table will automatically create a unique constraint for the specified column or columns. 如果删除DataTablePrimaryKey属性中的列,则会删除UniqueConstraintIf you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed.

下面的示例为DataTable的两列创建UniqueConstraintThe following example creates a UniqueConstraint for two columns of a DataTable.

Dim custTable As DataTable = custDS.Tables("Customers")  
Dim custUnique As UniqueConstraint = _  
    New UniqueConstraint(New DataColumn()   {custTable.Columns("CustomerID"), _  
    custTable.Columns("CompanyName")})  
custDS.Tables("Customers").Constraints.Add(custUnique)  
DataTable custTable = custDS.Tables["Customers"];  
UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[]
    {custTable.Columns["CustomerID"],
    custTable.Columns["CompanyName"]});  
custDS.Tables["Customers"].Constraints.Add(custUnique);  

请参阅See also