One to One relationship - column values are already determined

[albttx]

Version Information

Server Version: CLI Version (for CLI related issue):

CLI Version : v2.5.2 hasura docker image: hasura/graphql-engine:v2.3.1

I want to upsert data into a nested table, Here is my schemas.

CREATE TABLE users (
    id                  UUID NOT NULL UNIQUE PRIMARY KEY DEFAULT uuid_generate_v4(),
    email               VARCHAR(256) UNIQUE,
    password            VARCHAR(128),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at          TIMESTAMPTZ
);

CREATE TABLE user_companies (
    user_id             UUID UNIQUE NOT NULL PRIMARY KEY REFERENCES users(id),
    company_name        VARCHAR(256) NOT NULL,
    company_siret       INTEGER      NOT NULL UNIQUE,
    company_naf_code    VARCHAR(256) NOT NULL
);

This is the GraphQL Query

mutation ($user: users_insert_input!) {
  insert_user(object: $user, on_conflict: {
    constraint: users_pkey,
    update_columns: [id]
  }) {
    id
    user_company {
      company_name
    }
  }
}

{
  "user": {
    "id": "8922f66f-2306-419e-ab63-8835b9b72190",
    "user_company": {
      "data": {
        "company_name": "test2",
        "company_naf_code": "43",
        "company_siret": 43
      },
      "on_conflict": {
        "constraint": "user_companies_pkey",
        "update_columns": ["company_name"]
      }
      
    }
  }
}

And this is the output

{
  "errors": [
    {
      "extensions": {
        "path": "$.selectionSet.insert_user.args.object[0].user_company",
        "code": "validation-failed"
      },
      "message": "cannot insert object relationship \"user_company\" as \"id\" column values are already determined"
    }
  ]
}

Noticed that:

• This simple insert work

{
  "user": {
    "email": "[email protected]",
    "user_company": {
      "data": {
        "company_name": "test2",
        "company_naf_code": "42",
        "company_siret": 4
      }
    }
  }
}

• Using the email as constraint works, (I can upsert)

mutation ($user: users_insert_input!) {
  insert_user(object: $user, on_conflict: {
    constraint: users_email_key,
    update_columns: [email]
  }) {
    id
    user_company {
      company_name
    }
  }
}

{
  "user": {
    "email": "[email protected]",
    "user_company": {
      "data": {
        "company_name": "test2",
        "company_naf_code": "42",
        "company_siret": 42
      },
      "on_conflict": {
        "constraint": "user_companies_pkey",
        "update_columns": ["company_name"]
      }
      
    }
  }
}

[deathemperor]

can confirm issue happen on 2.8.x also confirmed the workaround works.

[kevinmichaelchen]

I've created a minimal reproduction of this issue here if anyone wants a simple fork.

https://github.com/kevinmichaelchen/hasura-8522

[dventimihasura]

@albttx I'm afraid the order of insertion matters. This is described in the documentation in a few places, but it's easy to overlook.

In this sub-section in **Mutations : Postgres : Insert : ** see the the !NOTE:

The order of object insertion can be controlled using the insertion_order option while creating a manual relationship. This is necessary to ensure nested inserts in one-to-one relationships are possible using either side as the parent which would otherwise error out with a Not-NULL violation error in one of the cases.

Following the links therein, we come to a sub-section with the sub-title **** where it's written:

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.

This can be changed via calls to the metadata API, however it might be simpler just to re-order the GraphQL insert operations. Instead of treating user_company as the nested object, treat user as the nested object:

mutation MyMutation {
  insert_user_company(objects: {company_name: "test42", user: {data: {email: "[email protected]"}}}) {
    returning {
      company_name
      user {
        email
        id
      }
    }
  }
}

!CAVEAT EMPTOR: I reversed the GraphQL mutation above by hand. It might have a typo. :grin:

[BenoitRanque]

The error is thrown from here. We explicitly check for any column overlap between columns that are part of the relationship definition, columns provided from the input, and columns provided from the parent insert.

This may be an oversight: as thing stand currently, it seems it's not possible to insert a parent record with a known value if that value is also a foreign key for a child record. Example:

Database schema:

CREATE TABLE "public"."user" (
  id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name TEXT,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE "public"."user_details" (
  id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  user_email TEXT NOT NULL UNIQUE REFERENCES "public"."user" (email),
  name TEXT
);

user table metadata.

table:
  name: user
  schema: public
object_relationships:
  - name: user_detail
    using:
      manual_configuration:
        column_mapping:
          email: user_email
        insertion_order: after_parent
        remote_table:
          name: user_details
          schema: public

user_details table metadata

table:
  name: user_details
  schema: public

Mutation

mutation InsertUser {
  insert_user (objects: {
    name: "bob",
    email: "[email protected]",
    user_detail: {
      data: {
        name: "bob"
      }
    }
  }) {
    affected_rows
  }
}

Error

{
  "errors": [
    {
      "extensions": {
        "code": "validation-failed",
        "path": "$.selectionSet.insert_user.args.objects[0].user_detail"
      },
      "message": "cannot insert object relationship \"user_detail\" as \"email\" column values are already determined"
    }
  ]
}

This feels like an unintended oversight, and perhaps something we should improve on.

Based on the test case for this error, this may be an unintended interaction caused by setting the insertion order. It seems the check intends to prevent child records from setting values for the parent record when the parent record is created in the same mutation. However in this case the insertion order is reversed, so this should insertion should be permitted.

Note that as a workaround, it's possible to reverse the mutation to insert the child object as parent (assuming we add the relationship):

mutation InsertUser {
  insert_user_details (objects: {
    name: "bob",
    user: {
      data: {
        name: "bob"
        email: "[email protected]",
      }
    }
  }) {
    affected_rows
  }
}

[manasag]

This has been addressed in v2.30.0-beta.1.

We now skip the check that relationship columns can be over-specified when the insertion order is 'after_parent', allowing the parent row to provide the key columns for a child relationship.

Commit: https://github.com/hasura/graphql-engine/commit/57690491af9669420307a69c6d26d6204016e14f