Partager via

ITableDefinitionWithConstraints::AddConstraint

  • Article

Adds a new constraint to a base table.

Syntax

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;
   DBORDINAL          cColumns;
   DBID               rgColumnList[];
   DBID              *pReferencedTableID;
   DBORDINAL          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 wasDBCONSTRAINTTYPE_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

Reference

ITableDefinitionWithConstraints::CreateTableWithConstraints

ITableDefinitionWithConstraints::DropConstraint