Creating Many-To-Many Relationships in Hasura

Question

I was trying to add a many-to-many relationship through a join table on a somewhat more complex set of models, so I tried dumbing it down when it wouldn't work into something with just two properties, and still can't get it to work.

Documentation I was attempting to follow for many-to-many from Hasura: https://hasura.io/docs/latest/graphql/core/guides/data-modelling/many-to-many.html#step-1-set-up-a-table-relationship-in-the-database

Say I want to have a relationship as follows:

• Many Cars which can have many drivers
• Many drivers whom can drive many cars

I created a car table which has:

1. id - Unique Integer, primary key
2. name - text

I created the driver table which has:

1. id - Unique Integer, primary key
2. type - text

I created the car_user table which has:

1. car_id - Integer, primary key
2. driver_id - Integer, primary key
3. id - Unique Integer (have tried with and without this)

Whenever I attempt to go and then add the foreign keys for the car_id and/or driver_id. I get an error as follows and have absolutely no idea how to solve it.

Answer 1

When creating the car_driver table you must set the id column to have the unique constraint.

So in the Hasura console the create should look like this:

Answer 2

I was getting the same error message, this worked for me:

There is a good example within the Hasura Console (not available on the Hasura website to my knowledge) --see image. It's much easier to implement the many-to-many with SQL.

Take the SQL in the example and modify to your needs (my mods at the bottom for example). You may get an error appear once you execute your own SQL, that's okay.

Refresh and navigate to your new schema (in this case I created a 'public' schema in the same SQL query). Then click track for 'Untracked tables or views' then track again for 'Untracked foreign-key relationships'.

You should now be able to add rows without any issues.

My example:

    CREATE SCHEMA public;

-- Create Tables
CREATE TABLE public.products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL
);

CREATE TABLE public.orders (
    id serial PRIMARY KEY,
    title text NOT NULL,
    author_name text NOT NULL
);

CREATE TABLE order_product (
    id serial PRIMARY KEY,
    order_id integer REFERENCES public.orders(id) NOT NULL,
    product_id integer REFERENCES public.products(id) NOT NULL
);