Modelling one-to-one table relationships¶
Table of contents
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 onepassport_info
- a
passport_info
has oneowner
Step 1: Set up a table relationship in the database¶
This one-to-one
relationship can be established in the database by:
- Adding a foreign key constraint from the
passport_info
table to theauthors
table using theowner_id
andid
columns of the tables respectively - Adding a unique constraint to the
owner_id
column for thepassport_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 theauthors
table usingid -> passport_info :: owner_id
- Object relationship,
owner
from thepassport_info
table usingowner_id -> authors :: id
Query using one-to-one relationships¶
We can now:
fetch a list of
authors
with theirpassport_info
:query { authors { id name passport_info { id passport_number } } }
query { authors { id name passport_info { id passport_number } } }{ "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 theirowner
:query { passport_info { id passport_number owner { id name } } }
query { passport_info { id passport_number owner { id name } } }{ "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¶
We can now:
- insert
passport_info
with theirowner
where theowner
might already exist (assume uniquename
forowner
):
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
}
}
}
}
Note
You can avoid the on_conflict
clause if you will never have conflicts.
Current limitations with nested one-to-one mutations¶
With one-to-one relationships, currently nested mutations will work only in one of the two directions.
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.
This is due to the way Hasura GraphQL engine currently handles nested mutations (described in detail
here). As nested object relations are inserted before the parent, the passport_info
will be attempted to be inserted first and the value of its owner_id
will be attempted to be set as the
id
of the author
. Due to this, based on whether the owner_id
of passport_info
is nullable or not, a
Not-NULL violation
error will be thrown either for the owner_id
field of passport_info
or the id
field of authors
.