3.3.2. Constraints

Constraints are objects that provide data integrity in tables. The PostgreSQL implements five different types of constraints being them: primary keys, foreign keys, unique keys, check and exclude. These constraints will be detailed in the next sub-sections. Graphically, in pgModeler database models, for each constraint in which a column is part a two-letter code will be appended to a string surrounded by « », this way the user is able to quickly identify which constraints the column is on. The two-letter codes for constraints are: pk (primary key), fk (foreign key), uq (unique), ck (check), ex (exclude) and nn (not null). The image below shows an example of these constraint codes.

In newer version of pgModeler the constraints object are shown in the table's extended attributes area (the bottom portion of them) in the same place where other objects like triggers, rules, indexes and policies are listed. In pgModeler, once created a constraint its type (primary key, foreign key, unique, etc) can't be changed due to its strong linking to relationship objects and the columns/constaints propagation mechanism (explained further in this documentation).

3.3.2.1. Primary key

Primary keys specifies that one or more columns of a table can store only unique (non-duplicated) and non null values. Technically speaking, a primary key is a combination of an unique and a not null constraints . The image below shows all the fields related to this kind of constraint.

Attribute Description
Fill Factor This attribute is related to how much (in a percentage from 10 to 100) the table / constraint pages are packed (in terms of physical space usage). This means that if your table is intended to have lots of updates you should use a small fill factor, on the other hand, static tables or least updated ones can use a greater fill factor to save disk space. Details about the fill factor attribute can be seen on PostgreSQL documentation.
Deferrable Specifies if the constraint can be deferred or not.
Deferral Specifies the deferral mode when the constraint is deferrable. Accepted values are INITIALLY IMMEDIATE and INITIALLY DEFERRED. Refer to table's DDL command documentation for details.
Columns This object's grid is used to set the columns that will form the constraint.

3.3.2.2. Foreign key

A foreign key is one of the most important constraint when it comes to data integrity. It requires that a group of columns in the source (or referrer) table only contains values that match the values in columns of the rows of the referenced table.

Attribute Description
Match Specifies how values inserted into the referencing column(s) is matched against the value of the referenced column on referenced table. Possible values are MATCH SIMPLE, MATCH FULL and MATCH PARTIAL. Refer to table's DDL command documentation for details.
Deferrable Specifies if the constraint can be deferred or not.
Deferral Specifies the deferral mode when the constraint is deferrable. Accepted values are INITIALLY IMMEDIATE and INITIALLY DEFERRED. Refer to table's DDL command documentation for details.
ON DELETE Indicates which action must be taken when an element on the referenced table is deleted. Accepted values are NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT. Refer to table's DDL command documentation for details.
ON UPDATE Acts like as ON DELETE but when an update event occurs.
Columns This tab is used to configure the referrer columns.
Referenced Columns This tab is used to configure the referenced table and columns.

3.3.2.3. Unique

Unique keys specifies that a group of columns of a table can contain only unique values and they are configured exactly in the same way as a primary key is.

3.3.2.4. Check

Check constraints specify expressions that produce a boolean result which new or updated rows must satisfy for an insert or update operation to succeed.

Attribute Description
No inherit Indicates that the object must not be propagated to the children tables of the owner of this constraint. This is related to generalization relationships and details about it can be found on section which details generalization relationships further in this document.
Check Expression A SQL expression that is evaluated every time a row is inserted or updated on the table. The expression must be true in order to the insert or update to succeed.

3.3.2.5. Exclude

The exclude 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. Internally, exclude constraints are implemented using indexes and the fields detailed below are almost the same as the ones found in the index editing form.

Attribute Description
Expression A SQL expression used as a predicate for the exclude constraint.
Fill Factor This attribute is related to how much (in a percentage from 10 to 100) the table / constraint pages are packed (in terms of physical space usage). This means that if your table is intended to have lots of updates you should use a small fill factor, on the other hand, static tables or least updated ones can use a greater fill factor to save disk space. Details about the fill factor attribute can be seen on PostgreSQL documentation.
Indexing Enables the index access method selection for the exclude constraint.
Deferrable Specifies if the constraint can be deferred or not.
Deferral Specifies the deferral mode when the constraint is deferrable. Accepted values are INITIALLY IMMEDIATE and INITIALLY DEFERRED. Refer to table's DDL command documentation for details.
Exclude Elements This group of fields is used to configure one or more exclude elements for the constraint. Each field are described below.
Column Column that composes the current exclude element. This attribute is mutually exclusive with Expression.
Expression A SQL expression that composes the current exclude element. This attribute is mutually exclusive with Column.
Operator Class An operator class associated with the exclude element. Refer to the section about operator classes to get details about this kind of object.
Operator An operator associated with the exclude element. Refer to the section operator to get details about this kind of object.
Sorting Enables the rows sorting on the exclude element.
Ascending Indicates ascending order when the sorting is enabled.
Descending Indicates descending order when sorting is enable.
Nulls first Indicates that null values must be sorted before non null values.

May 28, 2018 at 15:16