import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

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:

<GraphiQLIDE query={query { authors { id name passport_info { id passport_number } } }} response={{ "data": { "authors": [ { "id": 1, "name": "Justin", "passport_info": { "id": 1, "passport_number": "987456234" } }, { "id": 2, "name": "Beltran", "passport_info": { "id": 2, "passport_number": "F0004586" } } ] } }} />

• fetch a list of passport_infos with their owner:

<GraphiQLIDE query={query { passport_info { id passport_number owner { id name } } }} response={{ "data": { "passport_info": [ { "id": 1, "passport_number": "987456234", "owner": { "id": 1, "name": "Justin" } }, { "id": 2, "passport_number": "F0004586", "owner": { "id": 2, "name": "Beltran" } } ] } }} />

Insert using one-to-one relationships {#one-to-one-insert}

We can now:

• insert passport_info with their owner where the owner might already exist (assume unique name for owner):

<GraphiQLIDE query={mutation upsertPassportInfoWithOwner { insert_passport_info(objects: [ { passport_number: "X98973765", owner: { data: { name: "Kelly" }, on_conflict: { constraint: owner_name_key, update_columns: [name] } }, } ]) { returning { passport_number owner { name } } } }} response={{ "data": { "insert_passport_info": { "returning": [ { "passport_number": "X98973765", "owner": { "name": "Kelly" } } ] } } }} />

:::info 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.