Guaranteeing Uniqueness in Updating DataSet to Database

This section describes how the OracleDataAdapter configures the PrimaryKey and Constraints properties of the DataTable which guarantee uniqueness when the OracleCommandBuilder is updating DataSet changes to the database.

Using the OracleCommandBuilder object to dynamically generate DML statements to be executed against the database is one of the ways to reconcile changes made in a single DataTable with the database.

In this process, the OracleCommandBuilder must not be allowed to generate DML statements that may affect (update or delete) more that a single row in the database when reconciling a single DataRow change. Otherwise the OracleCommandBuilder could corrupt data in the database.

To guarantee that each DataRow change affects only a single row, there must be a set of DataColumns in the DataTable for which all rows in the DataTable have a unique set of values. The set of DataColumns indicated by the properties DataTable.PrimaryKey and DataTable.Constraints meet this requirement. The OracleCommandBuilder determines uniqueness in the DataTable by checking whether the DataTable.PrimaryKey is non-null or if there exists a UniqueConstraint in the DataTable.Constraints collection.

This discussion first explains what constitutes uniqueness in DataRows and then explains how to maintain that uniqueness while updating, through DataTable property configuration.

This section includes the following topics:

What Constitutes Uniqueness in DataRows?
Configuring PrimaryKey and Constraints Properties
Updating Without PrimaryKey and Constraints Configuration

What Constitutes Uniqueness in DataRows?

This section describes the minimal conditions that must be met to guarantee uniqueness of DataRows. The condition of uniqueness must be guaranteed before the DataTable.PrimaryKey and DataTable.Constraints properties can be configured, as described in the next section.

Uniqueness is guaranteed in a DataTable if any one of the following is true:

All the columns of the primary key are in the select list of the OracleDataAdapter.SelectCommand.
All the columns of a unique constraint are in the select list of the OracleDataAdapter.SelectCommand, with at least one involved column having a NOT NULL constraint defined on it.
All the columns of a unique index are in the select list of the OracleDataAdapter.SelectCommand, with at least one of the involved columns having a NOT NULL constraint defined on it.
A ROWID is present in the select list of the OracleDataAdapter.SelectCommand.

Note:

A set of columns, on which a unique constraint has been defined or a unique index has been created, require at least one non-nullable column for following reason; if all the columns of the column set are nullable, then multiple rows could exist which have a NULL value for each column in the column set. This would violate the uniqueness condition that each row has a unique set of values for the column set.

Configuring PrimaryKey and Constraints Properties

If the minimal conditions described in "What Constitutes Uniqueness in DataRows?" are met, then the DataTable.PrimaryKey or DataTable.Constraints properties can be set.

After these properties are set, the OracleCommandBuilder can determine uniqueness in the DataTable by checking the DataTable.PrimaryKey property or the presence of a UniqueConstraint in the DataTable.Constraints collection. Once uniqueness is determined, OracleCommandBuilder can safely generate DML statements to perform updates.

The OracleDataAdapter.FillSchema method attempts to set these properties according to this order of priority:

If the primary key is returned in the select list, it is set as the DataTable.PrimaryKey.
If a set of columns that meets the following criteria is returned in the select list, it is set as the DataTable.PrimaryKey.

Criteria: The set of columns has a unique constraint defined on it or a unique index created on it, with each column having a NOT NULL constraint defined on it.
If a set of columns that meets the following criteria is returned in the select list, a UniqueConstraint is added to the DataTable.Constraints collection, but the DataTable.PrimaryKey is not set.

Criteria: The set of columns has a unique constraint defined on it or a unique index created on it, with at least one column having a NOT NULL constraint defined on it.
If a ROWID is part of the select list, it is set as the DataTable.PrimaryKey.

Additionally, OracleDataAdapter.FillSchema exhibits the following behaviors:

Setting DataTable.PrimaryKey implicitly creates a UniqueConstraint.
If there are multiple occurrences of a column in the select list and the column is also part of the DataTable.PrimaryKey or UniqueConstraint, or both, each occurrence of the column is present as part of the DataTable.PrimaryKey or UniqueConstraint, or both.

Updating Without PrimaryKey and Constraints Configuration

If the DataTable.PrimaryKey or Constraints properties have not been configured, for example, if the application has not called OracleDataAdapter.FillSchema, the OracleCommandBuilder directly checks the select list of the OracleDataAdapter.SelectCommand to determine if it guarantees uniqueness in the DataTable. However this check results in a server round-trip to retrieve the metadata for the SELECT statement of the OracleDataAdapter.SelectCommand.

Note that OracleCommandBuilder cannot update a DataTable created from PL/SQL statements because they do not return any key information in their metadata.