3.3.2. Constraints
Constraints are objects that provide data integrity in tables. The PostgreSQL implements six different types of constraints, being them: primary keys, foreign keys, unique keys, check , exclude and not-null. These constraints will be detailed in the next sub-sections. Graphically, in pgModeler database models, for each constraint in which a column is a participant 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 in. 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.
The constraint objects are shown in the table's extended attributes area (the bottom portion) 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 the relationship objects and the columns/constraints propagation mechanism (explained further in this documentation).
3.3.2.1. Primary key
Primary keys specify 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 a unique and a not-null constraint. 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 the 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 constraints 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) are matched against the value of the referenced column in the referenced table. Possible values are MATCH SIMPLE, MATCH FULL, and MATCH PARTIAL. Refer to the 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 the 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 the table's DDL command documentation for details. |
ON UPDATE | Indicates which action must be taken when an element on the referenced table is updated. Accepted values are NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT. Refer to the table's DDL command documentation for details. |
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 specify 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 that 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 child tables of the owner of this constraint. This is related to generalization relationships and details about it can be found in the 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 in the table. The expression must be true so the insert or update succeeds. |
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. In pgModeler, an exclude constraint is composed of exclude elements which indicates which columns or expressions are used by the constraint. The exclude elements are handled in a different form in order to facilitate the visualization and handling (see below).
| 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 the 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 is described below. |
| Attribute | Description |
|---|---|
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 enabled. |
Nulls first | Indicates that null values must be sorted before non-null values. |
References
Table DDL
https://postgresql.org/docs/current/static/sql-createtable.html