DataTable 條件約束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 = false來停用此行為。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.

ForeignKeyConstraint DeleteRuleUpdateRule和屬性會定義當使用者嘗試刪除或更新相關資料表中的資料列時,所要採取的動作。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所設定的屬性而定,如果 DataSet 的EnforceConstraints屬性為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值設定為)。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方法來接受資料列的變更,或使用 DataSet、 DataTableDataRowRejectChanges方法來取消。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,以建立資料行的唯一條件約束。You can also create a unique constraint for a column by setting the Unique property of the column to true. 或者,將單一資料行的unique屬性設定為false ,會移除可能存在的任何 Unique 條件約束。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