3.10. Relationships

Relationships are the entities used to represent the link between two tables or between a table and a view. In order to provide easiness and flexibility when linking tables pgModeler implements nine different relationship types each one with its proper semantics, being them: one-to-one (1:1), one-to-many (1:n), many-to-many (n:n), self relationship, generalization or inheritance, copy, foreign key generated relationship, dependency and identifier.

For some kind of relationships (1:1, 1:n, n:n) there is a mechanism called column propagation which automatically creates columns and constraints in one of the involved tables. Columns propagated by the relationships are automatically named and graphically represented in different format (by default they are the ones with a green text color and italic font style). Normally these objects cannot be modified directly and in some cases pgModeler will raise errors if the user try any operation like delete or modification on them.

There are two special relationships that are created automatically in certain cases and cannot have some of their attributes handled directly by the user: foreign key generated relationships (in short, fk relationships) and relationships between tables and views which are used only to denote the dependency of the view to the linked table.

Relationships can have their own attributes and constraints where these latter are obligated to be associated to the former ones. The attributes are converted into columns when the relationship is validated and connected to the tables.

Finally, tables can have an optional or mandatory participation in the relationship known as cardinality. The cardinality affects directly all the objects generated by the relationship. In pgModeler relationships can be represented in two different notations: classical entity-relationship notation and crow's foot notation (see the image below). In newer versions of the tool the crow's foot is the default notation. In the next subsections all relationship types will be detailed.

3.10.1. One to one (1:1)

The semantics of this relationship is: “an item in table A has linked to it one item of table B”. In practice, foreign key columns in tableB are filled with the values of primary key columns of tableA. The restriction here is that two different items tableB can't reference the same item on tableA. In order to ensure this uniqueness a unique constraint is created and assigned to the foreign key columns in tableB. The column propagation is available for this kind of relationship and depends on the cardinality combination used as seen below:

Cardinality: tableA(0,1) —◇— tableB(0,1)

  • Columns, attributes and constraints are always added to the destination table (last selected one – tableB in the picture above) in order to represent the relationship.
  • The columns which compose the foreign key accept null values.
  • The policy for DELETE event on the foreign key is SET NULL.
  • The policy for UPDATE event on the foreign key is CASCADE.
  • An unique constraint is created using the same columns of the foreign key in order to represent the uniqueness of the entity tableA. This constraint is added to tableB.


Cardinalities: tableA(1,1) —◇— tableB(0,1) and tableA(0,1) —◇— tableB(1,1)

  • Columns, attributes and constraints are always added to the table which cardinality is (0,1) (in the sample image is the tableB).
  • The columns which compose the foreign key don't accept null values.
  • The policy for DELETE event on the foreign key is RESTRICT.
  • The policy for UPDATE event on the foreign key is CASCADE.
  • An unique constraint is created using the same columns of the foreign key in order to represent the uniqueness of the entity which the cardinality is (1,1).


Cardinality: tableA(1,1) —◇— tableB(1,1) [not implemented]

  • This cardinality combination is not implemented because requires merging the involved tables which may break the user's modeling. An error will be raised if the user persists in the creation of a relationship with this cardinality.

3.10.2. One to many (1:n)

The semantics of this relationship is: “an item on table A has linked to it many items of table B”. To clarify, unlike the one-to-one relationship, foreign key columns in tableB are filled with the values of primary key columns of tableA and tableB is allowed to have one or more items that references the same item on tableA. As the one-to-one relationship, the cardinality affects how columns and constraints are propagated as detailed below:

Cardinality: tableA(0,1) —◇— tableB(0,n)

  • Columns, attributes and constraints are always added to the destination table or to the (n) side (tableB in the sample image above) in order to represent the relationship.
  • The columns which compose the foreign key accept null values.
  • The policy for DELETE event on the foreign key is SET NULL.
  • The policy for UPDATE event on the foreign key is CASCADE.


Cardinality: tableA(1,1) —◇— tableB(0,n)

  • Columns, attributes and constraints are always added to the destination table or the (n) side (tableB in the sample image) in order to represent the relationship.
  • The columns which compose the foreign key don't accept null values.
  • The policy for DELETE event on the foreign key is RESTRICT.
  • The policy for UPDATE event on the foreign key is CASCADE.
Note: on the one-to-many relationships only the “1” side can have the cardinality changed. For the “n” side the cardinality is always the same (0,n).

3.10.3. Many to many (n:n)

The semantics of this relationship is: “many items on table A have linked to them many items of table B”. This kind of link creates an intermediate table to simulate the relationship between involved tables. Thus, the foreign key columns on intermediate table are filled with the values of the primary key columns of both tableA and tableB. The cardinality is ignored for this kind of relationship.

Cardinality: ignored

  • An intermediate table is created to represent this kind of relationship.
  • Two foreign keys are created on the intermediate table each one referencing the involved tables.
  • A primary key is created on the intermediate table using the columns that compose both foreign keys.
  • User-defined attributes and constraints are added to the intermediate table.
  • The policy for DELETE event on the foreign keys is RESTRICT.
  • The policy for UPDATE event on the foreign keys is CASCADE.
Note: the intermediate table can't be edited directly by the user so if you need to fine tune its attributes you need to convert the many-to-many relationship to an one-to-many from `tableA` to the intermediate table and another one-to-many that links `tableB` and the intermediate table. To make it quickly, right click the desired many-to-many relationship and select Convert.

3.10.4. Self relationship

Self relationship works likewise the regular 1:1, 1:n and n:n relationships, which means, all rules of cardinality and object creation applied to them are valid for this kind. The difference is that only one table is involved and it is linked to itself. To create a self relationship the user shoud press and hold Shift key when using the relationship tool before clicking the desired table. The image below shows an example of a self relationship.

3.10.5. Inheritance

This kind of relationship implements the table inheritance as PostgreSQL does. Visually, columns are copied from parent table to its the child, in the image below, from tableA to tableB. One point must be observed here, the columns are copied to child table but will not appear in the generated SQL code since is PostgreSQL the responsible for handling the inherited columns. Also, as in PostgreSQL, if the same columns exists on both tables they will be merged on the child only if their data types are the same. In practice, the columns on the child table are kept in their original configuration. When the columns are incompatible pgModeler will raise an error and abort the relationship creation.

Finally, when creating a generalization only check constraints are copied to child tables, if and only if the No inherit attribute is unchecked in these constraints. Other kinds of constraints aren't copied and the user should recreate them on the child table if they are needed thus it's quite important to be careful when handling multiple inheritance due to this situation.

3.10.6. Copy relationship

Copy relationship partially implements the table copy as PostgreSQL implements through the usage of the LIKE keyword. Unlike the inheritance, a copy relationship will not merge columns that already exist on the derivative tables, instead an error will be raised, this because the premise of copy is create a table completely decoupled from its parent one.

Observe the slight difference between an inheritance relationship on the previous section and the copy relationship on image below. The dashed line of the latter only denotes a transient dependency between the involved tables because the child depends on parent only at design time due to internal validation reasons. When the model is exported to SQL code two decoupled and independent tables will be created. Even the reverse engineering feature isn't able to identify if a table was originally created from a copy relationship.

3.10.7. FK relationship

This special relationship has the same semantics as the one-to-many but the user can't directly modify it, except for its basic attributes like name and position of the labels. This object is automatically created whenever the user manually adds a foreign key to a table, thus, pgModeler detects which table is being referenced in the constraint and link the two tables. Unlike the original one-to-many relationship this one doesn't accept any attribute or constraint. The only special behavior of this relationship is that if the user delete it from the model the foreign key that generated it will be wiped out from the table as well.

3.10.8. Dependency

This relationship is similar to the copy one in style and semantics but its applied to denote a link between a table and a view. Additionally, this object can't own attributes or constraints and the user can only handle its name and label position.

3.10.9. Identifier relationship

This relationship implements the concept of strong and weak entities. Basically, the weak entity (tableB in the image below) is the table which tuples only exists when the referenced tuples on strong entity (tableA) also exist. This means that if the item on the strong entity is deleted all the items on the weak entity that reference the deleted one are removed as well.

Additionally, this relationship creates a primary key on the weak entity hence the name identifier relationship. In case of a primary key already exists on the weak entity then the created primary key is merged into that first object. Finally, this kind of relationship is graphically identifiable by the thick line linked to the weak entity. Also, only one-to-one and one-to-many relationships can be marked as identifier.

3.10.10. Attributes and constraints

Attributes are visually represented by the orange circles that appear aside the relationship descriptor (in classical ER notation) or in the center of the line (in crow's foot notation) and are nothing more than columns assigned to the relationship. These attributes are inserted into the tables when the relationship is connected to the entities.

Relationship constraints are the same as the ones used for tables but the only difference is that they can only reference relationship attributes. These objects are added to tables likewise the attributes. Except for primary keys, all other types of constraints can be created for relationships. Primary keys are treated in a special way as described in the next section.

3.10.11. Editing form

Being the most complex objects in pgModeler, relationships have an editing form that tries to minimize this complexity by separating fields in different sections. This dialog is a polymorphic one, meaning that some fields will be absent depending on the relationship type. All the fields in the form are described as following.

Attribute Description
Custom color Specifies a custom color for the relationship line and descriptor.
Rel. Type This read-only field denotes the type of the relationship being handled: one-to-one (1-1), one-to-many (1-n), many-to-many (n-n), generalization or inheritance (gen), dependency or copy (dep) and fk relationship (fk).
Indentifier Marks the relationship as identifer. This option is available only for one-to-one and one-to-many relationships.
Cardinality Indicates which tables are of mandatory participation. This option is available only for one-to-one and one-to-many types relationships.
Reference Table Table from which columns are copied to the receiver table.
Receiver Table Table to where columns from reference table will be copied. For many-to-many relationship this field is renamed to Reference Table too since both tables serve as reference to create the intermediate table.
Gen. Table Name Sets the name of the intermediate table generated by a many-to-many relationship.
Foreign key settings This group of fields overrides the default configuration for any generated foreign key.
Copy Options Indicates which attributes must be copied from reference table when dealing with a copy relationship. You can indicate that the relationship must include or exclude some attributes from reference table or use the default behavior which is copy only column names, their data types and not-null constraints. Attributes that can be copied: DEFAULTS, CONSTRAINTS, INDEXES, STORAGE, COMMENTS or ALL.
Name Patterns This group of fields is used to define custom name patterns for each generated object. Details about name patterns are described in the next section. This option is available only for one-to-one, one-to-many and many-to-many relationships.
Attributes This tab is used to configure relationship attributes. This option is available only for one-to-one, one-to-many and many-to-many relationships.
Constraints This tab is used to configure relationship constraints. This option is available only for one-to-one, one-to-many and many-to-many relationships.
Primary key This tab is used to configure the special relationship primary key. This constraint uses exclusively the columns generated by the relationship as well its attributes to configure a primary key that will be added to the receiver table when connecting the relationship.
Advanced This tab lists all objects generated by the relationship or, in case of fk relationships, the foreign key that represents it. User can see details about these objects by opening them on their respective editing forms in read-only mode.

3.10.12. Name patterns

The column propagation is a powerful mechanism implemented by pgModeler. It can accelerate the productivity by automatically creating columns and constraints as the user link tables using relationships. The only downside of this feature is the lock in any created object, meaning that user can't change any of their attributes including names. In order to workaround this issue pgModeler has the ability to define name patterns that are parsed during relationship creation time and used to generate the name of any object allocated.

Basically, patterns are the joint of predefined tokens and additional characters added by the user. Four are the recognized tokens: {sc}, {st}, {dt} and {gt}. Note that any token must be enclosed in {} to be parsed by pgModeler. The meaning of each token is decribed below:

  • {sc}: stands for the name of the source column or the primary key column used as reference.
  • {st}: stands for the name of the source or reference table.
  • {dt}: stands for the name of the destination or receiver table.
  • {gt}: stands for the name of the generated (or most commonly, the intermediate) table name. This token is used only when the relationship is of type many-to-many.

To clarify the token usage, let's take a look on the image below. The relationship links tableA to tableB, in this case, the first is the source or reference and the last one the destination or receiver because it is that table which receives all generated objects. Note the column id_tableA on tableB, this column was generated through the pattern {sc}_{st} where {sc} is replaced by the primary key column id of the table tableA and {st} replaced by the name of reference table, again, tableA.

The same rule is valid for the generated foreign key tableA_fk, the name pattern used was {st}_fk. In this case there is a static text _fk that is kept when pgModeler translates the pattern, this because the text is not enclosed by {} as the rule states. The user can specify at most six name patterns, being them: two for columns, two for generated foreign keys, one for primary key and one for the generated unique key. Not all fields will be enabled at the same time this because the use of a specific name pattern is conditioned to the relationship type currently being handled.


Jun 21, 2018 at 17:20