Add constraints to tables

1. Click the Study Configuration icon from the navigation bar. Then click the Clinical Data Models tab.

2. Select the model and click Check Out.

   If the Check Out option is not active, you cannot check out the model because it is already checked out or you do not have the required privileges. To see who checked it out, look at the Checked Out By value at the left of the gray values near the top of the page. You may need to click the >> icon.

3. Select the table.

4. In the Constraints tab, click the Add icon.

5. Enter values:

   • Constraint: Enter a name for the constraint. It must be unique among constraints for the table and must not contain special characters or Oracle or SQL reserved words.

   • Description: (Optional)

   • Constraint Type: Select one:

     • Check: The check constraint allows you to specify allowable values for a particular column. Enter one allowed value in the Add Value field and click the arrow icon to move the value into the right-hand column; repeat for each value.

       If any row contains a different value for the column, the system does not insert the record but generates an error to the program writing to the table instance. If the program does not handle the error, the job fails.

     • Primary Key: (Required) A primary key is a column or set of columns whose values identify a row in a table as unique. The system uses the primary key to trace data lineage; see How the system tracks data lineage.

       Primary key columns cannot have a null value in any row.

       The system creates an index based on the primary key, which it uses to enforce a unique constraint and to speed up queries on the table.

     • Unique Key: A unique key is similar to a primary key in that it can include one or more columns whose values identify a row as unique. The difference is that the system allows null values in the columns that are part of a unique key.

       Any number of rows can include null (empty) values. A null in a column (or even all nullable columns in a composite unique key) satisfies the unique key constraint. However, you cannot have identical non-null values in the columns of a partially null composite unique key constraint.

     • Non-Unique Index: A non-unique index keeps rows sorted on the specified column or columns to speed up queries.

     • Bitmap Index: A bitmap index stores rowids (row IDs) associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid. If a particular bit is set, the row with the corresponding rowid contains the key value.

     Note:

     The Not Null constraint is handled as an attribute called Nullable for each column.

6. Columns: Specify the columns in the constraint by selecting them from the list on the left and using an Arrow icon to move them to the right.

7. Select Supports Duplicate to support inserting records with the same primary key value within a single data load, which may be required in a few cases. Selecting this option ensures that all records are loaded and not deleted but requires careful checking of the data. See Supporting duplicate primary key values in a load.

8. Click OK.

Tip:

If you create tables by uploading text files you can define constraints at the same time; see Required syntax for metadata files. InForm tables' constraints are imported as part of a metadata load and cannot be modified in the input model.

Back to Set up clinical data models