Add a Constraint to a Clinical Data Model Table

Use this API to add a constraint to a table in a study or library clinical data model.

Name

DME_PUB_DF_DATA_MODEL.AddConstraintToDataModelTab

Signature

procedure AddConstraintToDataModelTab(
p_api_version 	IN NUMBER
p_init_msg_list 	IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
p_commit 		IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
p_validation_level 	IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
x_return_status 	OUT NOCOPY VARCHAR2
x_msg_count 	OUT NOCOPY NUMBER
x_msg_data 	OUT NOCOPY VARCHAR2
pio_naming 	IN OUT NOCOPY cdr_naming_version_obj_type
pio_constraint 	IN OUT NOCOPY cdr_dm_tab_cons_obj_type
pi_constraintColumns IN OUT NOCOPY cdr_table_concols_list_coll
pi_vals 		IN cdr_vals_coll);

Parameters

This API has standard parameters (see Standard Parameters) and the following parameters:

• PIO_NAMING This is a parameter of type CDR_NAMING_VERSION_OBJ_TYPE. Enter values for the new constraint and the parent table. The following attributes are required:
  • COMPANY_ID: Company ID of the Data Model,
  • OBJECT_TYPE_RC: Enter '$OBJTYPES$TABLECNSTR'
  • NAME: Name of the constraint.

  • NAMESPACE_OBJ_ID: Object ID of the Data Model.

  • NAMESPACE_OBJ_VER: Object Version of the Data Model.
• PIO_CONSTRAINT (Mandatory): This is a parameter of type CDR_DM_TAB_CONS_OBJ_TYPE. Enter values as follows:
  • COMPANY_ID: To get your company ID, use CDR_PUB_DEF_FACTORY_UTILS.GetCompanyId.
  • OBJ_ID and OBJ_VER: Enter NULL.
  • CONSTRAINT_NAME: Enter a name.
  • CONSTRAINT_TYPE_RC: Identify the type of constraint:

    $CONSTRAINTYPES$CHECK

    $CONSTRAINTYPES$PRIMARYKEY

    $CONSTRAINTYPES$BITMAP

    $CONSTRAINTYPES$UNIQUE

    $CONSTRAINTYPES$NUINDEX

  • SURROGATE_KEY_FLAG_RC: Enter null. If you are creating a primary key and a surrogate key column does not exist in the table, the system creates it as CDR$SKEY. The column stores surrogate key values, which are required for data lineage tracing.
  • DUPLICATE_RECORD_FLAG_RC: The system uses this value only for primary keys. In most cases this should be set to '$YESNO$NO' but you can enable loading records with the same primary key value by entering '$YESNO$YES'. See the Oracle Health Sciences Data Management Workbench User's Guide for more information.
  • DM_CONS_COL_COLL: Pass NULL values.
• PI_CONSTRAINTCOLUMNS: This is a parameter of type CDR_TABLE_CONCOLS_LIST_COLL. Identify the table and the table's columns where you want to apply the constraints. Depending on the constraint, you must also provide values for attributes that define foreign key, or that identify the list of values object to store the values for a CHECK constraint.
  • TABC_COMPANY_ID: To get your company ID, use CDR_PUB_DEF_FACTORY_UTILS.GetCompanyId.
  • TABC_OBJ_ID and TABC_OBJ_VER: Enter NULL.

    FK_COL_COMPANY_ID: Enter NULL.

    FK_COL_OBJ_ID: Enter NULL.

    FK_COL_OBJ_VER: Enter NULL.

    POSITION: Enter the position of the constraint column in constraint.

    COL_COMPANY_ID: Enter the column company ID.

    COL_OBJ_ID: Enter the column's object ID.

    COL_OBJ_VER: Enter the column's object version.

    LOV_COMPANY_ID: Enter the column company ID.

    LOV_OBJ_ID: Enter NULL.

    LOV_OBJ_VER: Enter NULL.

• PI_VALS. This is a parameter of type CDR_VALS_COLL that is based on CDR_VAL_OBJ_TYPE type. It applies only to Check constraints. The type has two attributes for each value.
  • POSITION: The position of the value in the list.
  • VALUE: The valid value.