Skip to main content
Version: v2.x

Modeling One-to-One Table Relationships

Introduction​

A one-to-one relationship between two tables can be established via a unique foreign key constraint.

Say we have the following two tables in our database schema:

authors (
  id SERIAL PRIMARY KEY,
  name TEXT
)

passport_info (
  id SERIAL PRIMARY KEY,
  owner_id INT NOT NULL
  passport_number TEXT
  ...
)

These two tables are related via a one-to-one relationship. i.e.:

  • an author can have one passport_info
  • a passport_info has one owner

Step 1: Set up a table relationship in the database​

This one-to-one relationship can be established in the database by:

  1. Adding a foreign key constraint from the passport_info table to the authors table using the owner_id and id columns of the tables respectively
  2. Adding a unique constraint to the owner_id column for the passport_info table

This will ensure that the value of the owner_id column in passport_info table is present in the id column of the authors table and there will be only one row with a particular owner_id.

Step 2: Set up GraphQL relationships​

To access the nested objects via the GraphQL API, create the following relationships:

  • Object relationship, passport_info from the authors table using id -> passport_info :: owner_id
  • Object relationship, owner from the passport_info table using owner_id -> authors :: id

Query using one-to-one relationships​

We can now:

  • fetch a list of authors with their passport_info:
GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available
  • fetch a list of passport_infos with their owner:
GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available

Insert using one-to-one relationships​

We can now:

  • insert passport_info with their owner where the owner might already exist (assume unique name for owner):
GraphiQL
Query Variables
Request Headers
Documentation Explorer
No Schema Available
Note

You can avoid the on_conflict clause if you will never have conflicts.

Caveat for nested inserts​

Due to the way nested inserts are typically handled (described here), the order of object insertion needs to be specified using the insertion_order option while creating one-to-one relationships via the API. This is necessary to ensure nested inserts are possible using either side as the parent which would otherwise error out with a Not-NULL violation error in one of the cases.

In our example, inserting a passport_info with their nested owner will work seamlessly but trying to insert an author with their nested passport_info will throw a constraint violation error in case the insertion order is not specified for the owner object relationship.

Loading...
Loading...