How to work with relations and foreign keys between entities

Owned by Igor Gorchakov
Last updated: Feb 27, 2019
4 min read


MODDICONV-12 - Getting issue details... STATUS

In this article we will try to understand how to build usual relations between entities on a top of Raml-Module-Builder (RMB). Also we will see what kinds of indexes RMB can manage.

  • Relationships

One-to-One

In a one-to-one relationship, one record in a table is associated with one and only one record in another table.
Let's put we have person and passport entities, each passport is assigned to only one person. "persons" table contains a unique foreign key to "passports" table to guarantee that person refers to only one passport row:

To enable o2o relationship it's not enough to declare foreign key constraint in schema configuration. Unique index should be also declared on your foreign key ("persons" table):

During schema generation the "persons" table gets a real foreign key constraint, that references to the "_id"  field inside "passports" table. PostgreSQL manages referential integrity, so it does not allow to insert a new person that references to wrong passport row (PostgresClient throws GenericDatabaseException), however allows to insert persons without foreign key (when "passportId" is null). For additional information see PoC and test cases.

One-to-Many

In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each air plane can have many details. The foreign key field in the "details" table ("airplaneId") is designed to allow multiple instances of the same value:

To enable o2m relationship it's enough to declare only foreign key constraint in schema configuration ("details" table):

Unique index on foreign key is not declared, so "details" table accepts insertions of multiple rows with the same "airPlaneId" value.

During schema generation the "details" table gets a real foreign key constraint, that references to the "_id"  field inside "airplanes" table. PostgreSQL manages referential integrity, so it does not allow to insert a new detail that refers to non-existing air plane, however allows to insert details without foreign key (when "airPlaneId" is null). For additional information see PoC and test cases.

Many-to-Many

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between employees and locations: employee can purchase various locations to travel (buy a ticket), and location can be purchased by many employees: 

To enable m2m relationship you need to declare 2 foreign keys on related tables in schema configuration ("tickets" table):

During schema generation the "tickets" table gets 2 real foreign keys constraint, that reference to the "_id"  field inside "employees/locations" table. PostgreSQL manages referential integrity, so it does not allow to insert a new ticket that refers to non-existing location/employee, however allows to insert tickets without foreign keys (when "locationId" || "employeeId" is null). For additional information see PoC and test cases.

  • Constraints and indexes

    RMB does not directly support a foreign key inside the jsonb, but uses workaround to achieve referential integrity. If schema generator meats "foreignKeys" key in database configuration file (schema.json file in most of the cases), then it defines a separate column in a database table to store foreign key value, setup FK constraint on that field and creates trigger to keep it sync with the value inside the jsonb. The trigger runs every time before insert or update the target table, so keep in mind that the table with FK may be a bottleneck considering database performance.

Here is a source of the trigger and it's related function to keep sync foreign keys of the "tickets" table:



    RMB provides 5 kinds of indexes: 

  • likeIndex - fields in the json will be queried using the LIKE;
  • ginIndex - allows for regex queries to run in an optimal manner;
  • uniqueIndex - create a unique index on a field in the json;
  • btree index - supports equality and range queries on data that can be sorted into some ordering;
  • fullTextIndex - full text index using teh tsvector features of Postgres;

   For more information about indexes read here.

Unique constraint

RMB does not support unique constraint "out of the box" but we can emulate it using unique index to prevent duplicate fields in jsonb. Inserting json with the same filed value you will get an error "duplicate key value violates unique constraint "<unique_index_name>"".

Check constraint

RMB does not support check constraint "out of the box" for jsonb fields, but we can apply check constraint in customSnippet, for example: 
ALTER TABLE persons ADD CONSTRAINT test_constraint CHECK(((jsonb ->> 'weight')::numeric) > 75).