Constraints

On this page

Tables have a multitude of constraints between them. These constraints enforce the proper business semantics for the data in the database tables (relationships between data in different tables, semantical constraints of the data in the table). These available constraint types are as follows.

• Primary key constraints - specifying column (or a combination of columns), which uniquely identify the row in the table.
• Unique constraints. They are very similar to primary key constraints - uniquely identify the row in the table. One of the unique constraints of the table is designated as primary.
• Foreign key constraints, which establish relationships between two tables.
• Nullability constraints (NOT NULL constraint) - a simple constraint on the column, indicating that column must have value
• Check constraints establish additional checking conditions on values in the column / table.
• Assertions provide more global check than a check constraint - spanning multiple tables.
• Indexes are not constraints per se, but they are covered in this section because they are modeled similarly.

The primary keys, unique and check constraints, indexes can be modeled in two ways. One way is easy and simple but does not cover all the options provided by SQL. Another way is tedious, but provides full SQL coverage.

Implicit primary key, unique, check constraint, and index modeling

An easy way of modeling this kind of constraint is applying the «PrimaryKeyMember», «UniqueMember», «CheckMember», or «IndexMember» stereotype on the necessary column. PK, unique, and index markers can be applied on the column via its shortcut menu as shown in the following figure.

Quick application of PK, Unique, and Index markers.

To apply a check constraint marker on a column

1. Open the Specification window of the column.
2. Define the Condition property value in the In Check Constraint property group.

Thusly marked column is considered as a member of one-column constraint, specified in-line. It is by default an unnamed constraint. To specify its name, you need to define the Primary Key Name, the Unique Key Names, the Check Name, or the Index Names property value in the column Specification window.

In the SQL script (in CREATE TABLE, ADD COLUMN statements) this would correspond to the following part of the column specification.

<column name> [ <data type> ] ...
[ [<constraint name>] <constraint>... ]
<constraint> ::=
| UNIQUE| PRIMARY KEY
| CHECK '('<condition>')'

If primary key, unique constraint or index must span several columns (in this case constraint is not in-line, near the column definition, but as a separate definition at the bottom of the table definition), all the columns must be marked with the appropriate «UniqueMember» / «IndexMember» stereotype and all must have the same name. Column can participate in several unique. Various cases of quick constraint modeling are depicted in the following figure.

Various situations, modeled with quick constraint notation.

Explicit primary key, unique, check constraint, and index modeling

The quick, implicit way to model constraints does not cover some cases, allowed by SQL. Constraints in SQL can be marked as DEFERABLE, INITIALY DEFERRED; constraint in the database can be in active state (enforced) or disabled. Indexes have various configuration parameters.

Modeling with the help of «XYZMember» stereotypes does not allow to specify this additional information. In this case modeling with explicit constraint model elements is necessary. This can be done from the Specification window of table. There are separate tabs for creating these constraint elements: Unique Constrains (allows creating both primary keys and unique constraints), Check Constraints, Indices. Once created, additional properties of the constraints can be specified.

Besides the standard SQL element properties, primary key and unique constraint have following properties

Check constraints have the same properties as primary key and unique constraints, and additionally have following properties available in the Specification window.

Besides the standard SQL element properties, index has the following properties available in the Specification window.

Foreign keys

Foreign key example.

Foreign keys describe relationships between two tables. At the detailed understanding level, foreign key is a constraint on the (group of) columns in the source / referencing table, such that for each row in the source table their value combination (tuple) is equal to the value combination (tuple) of the (group of) columns for some row in the target / referenced table.

Foreign keys also have the two ways to be modeled. The main way is described below.

The main way to model foreign keys is to draw association relationship from the referencing table to the referenced table. The relationship can be simply draw in the diagram from the smart manipulator or from the button in the diagram toolbar.

When the FK association is drawn, the following dialog pops up.

Foreign key dialog.

Note that you have to have the necessary columns in the tables (PK or unique columns in target table, referencing FK columns in the source table) before drawing the FK relationship. In this dialog, select the referenced columns (of the target table) in the first column of the table, and corresponding referencing columns (of the source table). Additionally, foreign key name can be specified.

When dialog is OK’d, foreign key association is created; «FK» stereotype is applied on the referencing association end and the selected column information is stored in tags.

If foreign key information has to be changes, this is done in the Specification window of the FK property. Besides the standard SQL element properties foreign key has the following properties available in the Specification window.

The alternative way of modeling a foreign key is creating a UML constraint with the «ForeignKey» stereotype applied. This way is less desired than the main way, because it does not visualize relationship between tables. It is just a constraint in the table. This method may be used when human-readability is not critical, e.g., when database layout is generated with some custom automated script / transformation in the model.

To create a constraint with the «ForeignKey» stereotype

1. Select a table in the Containment tree.
2. Do one of the following.

• Right-click the selected element and from its shortcut menu select New Element > Explicit Foreign Key.
• Open the Explicit Foreign Keys tab in the table’s Specification window.

Besides the standard SQL element properties and properties that are available for other explicit constraints (that is, PK, unique, check constraints), explicit foreign key has the following properties available in the Specification window.

Nullability constraint

Nullability, or NOT NULL constraint forces the condition that the column must have value. Implicit NOT NULL constraint is modeled with the nullable field of the column (set nullable=false to specify NOT NULL). This is a usual and quick way to model these constraints.

Usually, there is no need to model these constraints explicitly - create a separate model element for them. But in the more complex cases these constraints can be created by hand and the «NotNullConstraint» stereotype applied on them. This allows specifying non-inline constraints, or named constraints, or deferred constraints or inactive constraints.

NOT NULL constraint does not have any additional properties in the Specification window besides the properties that all table constraints have.

Assertion

Assertion constraints are very similar to check constraints, but instead of being local to the table, they are global to the database. Assertions check some condition that must hold through several tables. Assertions are modeler as explicit constraints; there is no shorthand modeling form - assertion is always an explicit UML constraint.

To create an assertion

1. Select a schema or a database element in the Containment tree.
2. Right-click the selected element and from its shortcut menu select New Element > Assertion.

Besides the standard SQL element properties assertion has the following properties available in the Specification window.

Triggers

Trigger describes some action that must be performed when some particular data manipulation action is being performed in the database table. Trigger can be fired when data is added, deleted or changed in the table and perform some action (update some other table, calculate additional values, validate data being updated or even change the data that is being updated).

Trigger is always defined for some table. You can define triggers in the Triggers tab of the table Specification window. Trigger has an event type (on what actions trigger is fired, that is, on insert, on update, or on delete), action time (before, after, instead of), and an actual body describing the actions.

Besides the standard SQL element properties, trigger has the following properties available in the Specification window.