ITableDefinitionWithConstraints::AddConstraint

OLE DB Programmer's Reference

Adds a new constraint to a base table.

HRESULT AddConstraint(    DBID               *pTableID,    DBCONSTRAINTDESC   *pConstraintDesc);

Parameters

pTableID

[in]
A pointer to the DBID of the table to which the constraint is to be added.

pConstraintDesc

[in]
A pointer to the DBCONSTRAINTDESC structure that describes the new constraint. DBCONSTRAINTDESC is defined as follows:

typedef struct tagDBCONSTRAINTDESC {    DBID              *pConstraintID;    DBCONSTRAINTTYPE   ConstraintType;    ULONG              cColumns;    DBID               rgColumnList[];    DBID              *pReferencedTableID;    ULONG              cForeignKeyColumns;    DBID               rgForeignKeyColumnList[];    OLECHAR           *pwszConstraintText;    DBUPDELRULE        UpdateRule;    DBUPDELRULE        DeleteRule;    DBMATCHTYPE        MatchType;    DBDEFERRABILITY    Deferrability;    DB_URESERVE        cReserved;    DBPROPSET          rgReserved[]; } DBCONSTRAINTDESC;

The elements of this structure are used as described in the following table.

Element	Description
pConstraintID	The constraint identifier. If this is null, the provider generates a unique ID for the constraint. This ID is not returned to the consumer through the DBCONSTRAINTDESC structure. Consumers should generally specify a value for the constraint and not set pConstraintID to null, because there is no easy way to get back the ID for the added constraint.
ConstraintType	The type of the constraint as defined in the DBCONSTRAINTTYPE enum. One of the following values: DBCONSTRAINTTYPE_UNIQUE DBCONSTRAINTTYPE_FOREIGNKEY DBCONSTRAINTTYPE_PRIMARYKEY DBCONSTRAINTTYPE_CHECK
cColumns	The number of elements in the array passed in rgColumnList. For check constraints, this is always zero. If cColumns is zero, rgColumnList is ignored.
rgColumnList	For unique and primary key constraints, this contains the list of columns that comprise the constraint. For foreign key constraints, this defines the list of column IDs (DBIDs in the referenced table) that make up the referenced key in a relationship. The order of the elements in this array comprise the key columns in descending order of significance. As such, the first element is the most significant key column and the last element in the array is the least significant key column.
pReferencedTableID	For foreign keys, this contains the referenced table in a relationship. For all other types of constraints, this is null.
cForeignKeyColumns	The number of elements in the array passed in rgForeignKeyColumnList. If cForeignKeyColumns is zero, rgForeignKeyColumnList is ignored. This field must be zero if pReferencedTableID is a null pointer.
rgForeignKeyColumnList	The columns (DBIDs in the base table) that comprise the foreign key in a relationship. The order of the elements in this array comprise the key columns in descending order of significance. As such, the first element is the most significant key column and the last element in the array is the least significant key column. This field is ignored if cForeignKeyColumns is zero.
pwszConstraintText	The constraint clause. If ConstraintType is not DBCONSTRAINTTYPE_CHECK, this must be a null pointer.
UpdateRule	The update rule (as defined in SQL-92). One of the following values: DBUPDELRULE_NOACTION DBUPDELRULE_CASCADE DBUPDELRULE_SETNULL DBUPDELRULE_SETDEFAULT This field is ignored unless ConstraintType is DBCONSTRAINTTYPE_FOREIGNKEY.
DeleteRule	The delete rule (as defined in SQL-92). One of the following values: DBUPDELRULE_NOACTION DBUPDELRULE_CASCADE DBUPDELRULE_SETNULL DBUPDELRULE_SETDEFAULT This field is ignored unless ConstraintType is DBCONSTRAINTTYPE_FOREIGNKEY.
MatchType	The match type (as defined in SQL-92). This field is ignored unless ConstraintType is DBCONSTRAINTTYPE_FOREIGNKEY. One of the following values: DBMATCHTYPE_NONE DBMATCHTYPE_FULL DBMATCHTYPE_PARTIAL
Deferrability	A bitmask that describes whether application or checking of the constraint is immediate or deferred. Values are as follows: DBDEFERRABILITY_DEFERRED—Application of the constraint is deferred. This bit is ignored unless DBDEFERRABILITY_DEFERRABLE is set. DBDEFERRABILITY_DEFERRABLE—Application of the constraint is deferrable. Not specifying DBDEFERRABILITY_DEFERRED implies that the constraint is immediate. Not specifying DBDEFERRABILITY_ DEFERRABLE implies that the constraint is not deferrable. If DBDEFERRABILITY_DEFERRABLE is not specified, the DBDEFERRABILITY_DEFERRED bit is ignored and the constraint is immediate. If the constraint is immediate, the constraint is applied or checked at the end of each SQL statement. If the constraint is deferred, the constraint is applied or checked when the constraint is changed to immediate, either explicitly by command execution or implicitly at the end of the current transaction.
cReserved	The number of DBPROPSET structures in rgReserved. If this is zero, the provider ignores rgReserved. This parameter applies to constraint properties, and consumers should set this element to 0.
rgReserved	An array of DBPROPSET structures containing properties and values to be set. If cReserved is zero, this argument is ignored. This parameter applies to constraint properties, and consumers should set this element to NULL.

Return Code

S_OK

The method succeeded.

E_FAIL

A provider-specific error occurred.

E_INVALIDARG

pTableID or pConstraintDesc was a null pointer.

cForeignKeyColumns was not zero, and rgForeignKeyColumnList was null.

cForeignKeyColumns was not zero and was not equal to cColumns.

cColumns was not zero, and rgColumnList was null.

cReserved was not zero, or rgReserved was not a null pointer.

DB_E_NOCOLUMN

The DBCONSTRAINTDESC structure contained a reference to a column ID, either in rgForeignKeyColumnList, when rgForeignKeyColumnList is not ignored, or in rgColumnList, and the column was not found. This error can also be returned when the column referred to by pwszConstraintText does not exist.

DB_E_BADCONSTRAINTFORM

ConstraintType was not DBCONSTRAINTTYPE_FOREIGNKEY, and cForeignKeyColumns was not zero.

ConstraintType was not DBCONSTRAINTTYPE_FOREIGNKEY, and pReferencedTableID was not a null pointer.

ConstraintType was DBCONSTRAINTTYPE_FOREIGNKEY, and pReferencedTableID was NULL.

The type of the constraint was not a check constraint, and pwszConstraintText was not NULL.

ConstraintType was DBCONSTRAINTTYPE_CHECK, and pwszConstraintText was a null pointer.

ConstraintType was DBCONSTRAINTTYPE_CHECK, and cColumns was not zero.

ConstraintType was DBCONSTRAINTTYPE_FOREIGNKEY, and cForeignKeyColumns was zero.

ConstraintType was DBCONSTRAINTTYPE_FOREIGNKEY, and the column type of a column in rgForeignKeyColumnList does not match the type of the corresponding column in rgColumnList.

ConstraintType was DBCONSTRAINTTYPE_FOREIGNKEY, DBCONSTRAINTTYPE_PRIMARYKEY, or DBCONSTRAINTTYPE_UNIQUE; and cColumns was zero.

DB_E_BADCONSTRAINTID

pConstraintID was invalid.

DB_E_BADCONSTRAINTTYPE

ConstraintType was invalid or not supported by the provider.

DB_E_BADDEFERRABILITY

Deferrability was invalid, or the value was not supported by the provider.

DB_E_BADMATCHTYPE

MatchType was not ignored and was invalid, or the value was not supported by the provider.

DB_E_BADUPDATEDELETERULE

UpdateRule or DeleteRule was not ignored and was invalid, or the value was not supported by the provider.

DB_E_DUPLICATECONSTRAINTID

pConstraintID was the same as an existing constraint.

ConstraintType was DBCONSTRAINTTYPE_PRIMARYKEY, and the base table already had a primary key.

DB_E_NOTABLE

The table specified by *pTableID does not exist in the data store.

The table specified by *pReferencedTableID in the DBCONSTRAINTDESC structure does not exist.

DB_E_SCHEMAVIOLATION

The type of the constraint conflicted with an attribute (for example, column type) of the referenced column.

The constraint conflicts with current contents of the table.

DB_E_TABLEINUSE

The specified table was in use, and the provider could not add a constraint as a result.

DB_SEC_E_PERMISSIONDENIED

The consumer did not have sufficient permission to add a constraint.

XACT_E_XTIONEXISTS

The provider supports transactional DDL, the session is participating in a transaction, and the value of DBPROP_SUPPORTEDTXNDDL is DBPROPVAL_TC_DML.

Comments

If AddConstraint returns any errors, the constraint is not created.

If the session is participating in a transaction, if DBPROP_SUPPORTEDTXNDDL is DBPROPVAL_TC_DDL_IGNORE, and if the method succeeds, the operation is complete and is unaffected by subsequent calls to abort or commit the transaction.

If the session is participating in a transaction, if DBPROP_SUPPORTEDTXNDDL is DBPROPVAL_TC_DDL_COMMIT, and if the method succeeds, the transaction is committed without retention. No new transaction is created. Any new work done on the session is outside the scope of a transaction. Attempting to explicitly commit or abort when there is no outstanding transaction returns an error.

The following example illustrates which parameters should contain the base table and referenced table DBIDs when defining constraints in a foreign key relationship: Let Orders be the base table, and Customers the referenced table. The CustID field of Orders has a foreign key constraint on the CustID field of Customers. The rgColumnList parameter contains the DBID for Customers.CustID and rgForeignKeyColumnList contains the DBID for Orders.CustID.

See Also