EPUB | CHM | PDF

Constraint Editor

Top Previous Next

Constraint Editor allows you to define and modify check, unique and primary key constraints on the table. The Constraint Editor is placed within the Table Editor dialog. Click the Constraints tab of the Table Editor to manage table constraints.

The main area of the editor is the list of constraints defined on table. The columns of the list allow you to modify the properties of the particular constraint.

editor-table-constraint

These properties are:

Constraint Name

On optional name for the constraint. If you don't specify a name, the system chooses a name for you.

Fields

A list of columns on which the current constraint is defined. This field is actual only for primary keys, unique and exclude constraints.

tip For EXCLUDE constraints one may add expressions not only columns on which constraint will be built. Comparison operators are specified using drop-down field editor either.

Kind

A kind of the constraint. You can choose one of the following kinds:

EXCLUDE. Exclusion constraint guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

CHECK. Is the most generic constraint type. It allows you to specify that the value in certain columns must satisfy a Boolean (truth-value) expression.

UNIQUE. The unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table.

PRIMARY KEY. Simply put, it's a combination of a unique constraint and a not-null constraint. Please note, that you could define primary keys right in the Column Editor.

The properties pane allows you to define the advanced properties of the constraint, selected in the Constraint List. The appearance of this pane changes according to the kind of the constraint. These properties are:

Expression

This field can be used for setting boolean expression for a check constraint. The check expression should involve the column thus constrained, otherwise the constraint would not make too much sense. For exclusion constraint it allows you to specify an constraint on a subset of the table; internally this creates a partial index. Example expression:

discounted_price > 0

Deferable

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction. Currently, only UNIQUE, PRIMARY KEY, and EXCLUDE constraints accept this clause. CHECK constraints are not deferrable.

Method

Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class for the index access method.

Fill Factor

Specifies storage parameter for underlying index. The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.

Tablespace

The tablespace in which to create the underlying index.

FK Referenced

Read only checkbox showing if unique or primary key constraint is used in the reference join and cannot be modified.

Comment

An arbitrary description for the constraint.

The buttons under the list allow you to perform the following actions:

§Add - add a new constraint to the end of the list;
§Duplicate - add a new constraint with the same properties as the selected constraint to the end of the list;
§Delete - remove the selected constraint from the list;
§Up/Down - move the selected constraint along the list.