.. raw:: latex

  \appcount

.. _logical_relationship_erd:

Logical Relationships in ER Diagrams
======================================

In an ER diagram, a relationship between tables can be either physical or logical.

A physical relationship corresponds to a foreign key in the database. When creating 
such a relationship, the editor creates the corresponding foreign key constraint by 
generating an SQL ``ALTER TABLE`` statement.

A logical relationship exists only in the ER model. 
It represents an intended relationship between tables and 
columns but does not create a foreign key or any other constraint in the database.

The main differences between physical and logical relationships are as follows:

.. tabularcolumns:: |>{\arraybackslash}\X{7}{17}|>{\arraybackslash}\X{5}{17}|>{\arraybackslash}\X{5}{17}|
.. list-table::
   :class: longtable
   :header-rows: 1

   * - Property
     - Physical relationship
     - Logical relationship
   * - Creates a foreign key
     - Yes
     - No 
   * - Generates an SQL ``ALTER TABLE`` statement
     - Yes
     - No
   * - Stored in `.eqd`
     - Yes
     - Yes
   * - Displayed on the diagram
     - Yes
     - Yes; by default, as a dotted line
   * - Can be imported from JSON
     - No
     - Yes

.. rubric:: Importing Logical Relationships from JSON
  
Logical relationships can be added to an ER diagram by importing them from a JSON file.

During the import, the editor creates logical relationships between the specified tables 
and columns. These relationships are displayed on the diagram as dotted lines but do not 
create foreign keys or other constraints in the database.

The import file must contain valid JSON encoded in UTF-8.
The root element must be an array. Each array element describes one logical relationship.

For example:

.. code-block::

  [
    {
      "name": "CUSTOMER_ORDER",
      "sourceTable": "ORDERS",
      "sourceColumn": "CUSTOMER_ID",
      "targetTable": "CUSTOMERS",
      "targetColumn": "ID" 
    },
    {
      "sourceTable": "ORDER_ITEMS",
      "sourceColumn": "ORDER_ID",
      "targetTable": "ORDERS",
      "targetColumn": "ID" 
    }
  ]


A logical relationship is directed from the source table to the target table.

The object fields are:

- ``"name"`` - the display name of the relationship (optional);
- ``"sourceTable"`` - the source table;
- ``"sourceColumn"`` - the source column;
- ``"targetTable"`` - the target table;
- ``"targetColumn"`` - the target column.

The values of all the fields listed above must be strings. Additional fields in objects are ignored.

The "name" field is not used to determine relationship uniqueness.
Two relationships are considered identical if all four endpoints match: 
the source table, source column, target table, and target column.

For known Firebird system objects (RDB$... and MON$...), the editor contains 
a built-in set of logical relationships. These relationships are displayed 
automatically on the diagram when both objects participating in the relationship 
are present on the diagram.
These relationships do not create constraints in the database and 
cannot be deleted by the user.