Relations between SQL tables

When an SQL table references another SQL table, you need to define a foreign constraint for the referencing table. This information is also required in the DB Sync database schema, and must be provided in "references" property of table definitions:

        {
            "ordinal": 9,
            "name": "employee",
            ...
            "references": [ ... ],
            ...
        }

The "references" record must contain the following properties:

• name: The name of the foreign key constraint.
• cols: The list of column names referencing values in the referenced table.
• ref_type: The type of reference. Values can be "master" or "property".
• ref_table: The name of the referenced table.
• ref_cols: The list of columns corresponding to cols.
• on_delete: The action to perform when deleting a row. Values can be "deny", "cascade", "null" or "default".
• on_update: The action to perform when updating a row. Values can be "deny", "cascade", "null" or "default"

A foreign key relation can be of type master or of type property. Depending on the type of the foreign key relation, the DB Sync system will reduce data rows differently when using data filters:

• master: The relation between the referencing table and the referenced table is a detail->master relation. For example, when a cclines table (containing order lines) has a ccl_cusord column referencing ids of a cusord_id column in a cusord table (containing order header information), cclines is the detail table of the cusord master table.
  Regarding data filters:

  • If a master row is excluded by a data filter, all detail rows are excluded.
  • If one of the detail rows is excluded by a data filter, the referenced master row will also be excluded.

• property: The relation between the referencing table and the referenced table is an object->property relation. For example, when an employee table has an emp_shop column referencing ids of a shop_id column in a shop table, employee is considered as the object table, while shop is the property table.

  Regarding data filters:

  • If a property row is excluded by a data filter, all referencing object rows will also be excluded.
  • If an object row is excluded by a data filter, the referenced property row will not be excluded.

                {
                    "name": "fk_employee_shop",
                    "cols": [
                        "emp_shop"
                    ],
                    "ref_type": "property",
                    "ref_table": "shop",
                    "ref_cols": [
                        "shop_id"
                    ],
                    "on_delete": "deny",
                    "on_update": "deny"
                }

Below is an example showing a detail->master table relation, in the cclines table definition, referencing the cusord master table:

                {
                    "name": "fk_cclines_cusord",
                    "cols": [
                        "ccl_cusord"
                    ],
                    "ref_type": "master",
                    "ref_table": "cusord",
                    "ref_cols": [
                        "cusord_id"
                    ],
                    "on_delete": "deny",
                    "on_update": "deny"
                }