One to One relationship causing Not-Null Constraint Violation on Nested Data Insert

[emahuni]

{
  "errors": [
    {
      "extensions": {
        "path": "$.selectionSet.insert_a.args.objects[0]",
        "code": "constraint-violation"
      },
      "message": "Not-NULL violation. null value in column \"id\" violates not-null constraint"
    }
  ]
}

The following table definitions reproduce the above error:

Tables

Table a:

Table B:

Relationships

Table A:

Table B:

Invoking The Error

That error happens when inserting data from the "has one" side (table a), but not when inserting from the "belongs to" side (I wish Hasura could label relationships like this as well in addition to what's there to make things clear quickly):

the above error happens when you run this query:

mutation {
  insert_a(objects: {
    junk_a: "This is a's junk"
    b:{
      data: 
        {
          junk_b: "B's junk inserted when inserting a's junk"
        }
    }
  }){
    affected_rows
  }
}

but not when you run this query from the other side:

mutation {
  insert_b(objects: {
    junk_b: "This is B's junk"
    a:{
      data: 
        {
          junk_a: "A's junk inserted when inserting B's junk"
        }
    }
  }){
    affected_rows
  }
}

This could be related to #2508, but I am not sure cause what he is reporting on is different.

[emahuni]

It's also good note that when you change the relationship to be a "one-to-many" making it such that "A" hasMany "B" by removing the unique constraint on the foreign key constraint, the error disappears

[macalinao]

i'm getting this error too

[howientc]

I had issues similar to this. After doing some research, setting DEFERRABLE INITIALLY DEFERRED on my Foreign Key definition (added this manually to my initial migration script) fixed the issue.

[emahuni]

I had issues similar to this. After doing some research, setting DEFERRABLE INITIALLY DEFERRED on my Foreign Key definition (added this manually to my initial migration script) fixed the issue.

can you pliz give an example script

[dohomi]

same error for me. Any solutions for one-to-one relations?

[dohomi]

@howientc do you have some more infos how you did this?

[0x777]

The server currently does not distinguish object relationships into 'many-to-one' and 'one-to-one' relationships. It currently treats all object relationships as 'many-to-one' because of which the ordering of inserts when there are one-to-one relationships is incorrect. This should not be hard to fix.

When defining an object relationship using manual configuration we can add an extra field which indicates the type of the object relationship? Something like this:

{
  "type": "create_object_relationship",
  "args": {
    "table": "author",
    "name": "setting",
    "type": "one_to_one",
    "using": {
      "manual_configuration": {
        "remote_table": "author_setting",
        "column_mapping": {
          "id": "author_id"
        }
      }
    }
  }
}

The type key when absent defaults many_to_one (and also you can only define one_to_one relationships using manual_configuration for now). So when the console infers 'one to one' relationships using foreign key constraints, it can use the above configuration.

What would be great though is to add support at the server to determine 'one-to-one' relationships automatically which can be done after this.

[0x777]

After further discussion with @rikinsk we finalised on having insertion_order when you are defining a manual object relationship.

{
  "type": "create_object_relationship",
  "args": {
    "table": "author",
    "name": "setting",
    "type": "one_to_one",
    "using": {
      "manual_configuration": {
        "remote_table": "author_setting",
        "column_mapping": {
          "id": "author_id"
        },
        "insertion_order": "after_parent"
      }
    }
  }
}

insertion_order can take two values, before_parent (default and the current behaviour) or after_parent which is needed for 'one-to-one' relationship inserts.

[dohomi]

@0x777 for now all I do is to make sure to call the mutation based on where the foreign key constraint is saved. But you are saying that with a next update this will be possible from both sides? Is there any roadmap to track process?

[0x777]

@dohomi

But you are saying that with a next update this will be possible from both sides?

Yes. You can follow this issue. We will share a link to a PR build when it is ready.

[beepsoft]

@0x777 Is this issue still being worked on? Do you have an estimate when this could be fixed? Thanks!

[indatawetrust]

I get this error in the latest version. Is there any information on when to fix it?

[indatawetrust]

@marionschleifer @0x777 @emahuni @beepsoft

I think I found the cause of the error. In the same request, I see the following logs where I send "x-hasura-admin-secret" and "Authorization" together.

and I get an error.

I only see the following logs when I send "Authorization" value in the same request.

and the query runs successfully.

I think a lot of people are experiencing it. It may be useful to indicate this in the documentation or to make an improvement. thanks for this beautiful tool :)

[danausx]

I had issues similar to this. After doing some research, setting DEFERRABLE INITIALLY DEFERRED on my Foreign Key definition (added this manually to my initial migration script) fixed the issue.

can you pliz give an example script

I have taken this approach as well. I was getting:
cannot insert object relation ship \"boatByBoat\" as \"boat\" column values are already determined"
with a One to One relationship and a fkey constraint.

I removed the one to one relationship and manually added a one to many (Array relationship) with the UI, afterwards I was getting a fkey constraint error instead:
Foreign key violation. insert or update on table \"boats\" violates foreign key constraint \"boats_customer_fkey\.

I have removed the Foreign Key constraint that I added with the UI and manually added one with DEFERRABLE INITIALLY DEFERRED. The fkey links the two elements and Behavior of fkeys is as expected but now nested data inserts work fine even if they are inserted in a specific order as mentioned by documentation. Here is the SQL I used to manually add the fkey form the Hasura "SQL" option on the bottom left of the the Data panel:
ALTER TABLE boats ADD CONSTRAINT boats_customer_fkey1 FOREIGN KEY (customer) REFERENCES customers (id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;

This now works for me:
https://hasura.io/docs/1.0/graphql/manual/mutations/insert.html#insert-an-object-along-with-its-related-objects-through-relationships

[EmrysMyrddin]

@0x777 Is this still an issue you are working on ? Can we help in some way ? This is not very handy in lots of situations

[AtjonTV]

I just stumbled across this issue during the implementation of my API into my Android App.

I have a bills table (table A) and a few subtables (table B) and only the subtables reference to the bills table.

And I have the case where I want to insert through table A but hit this exact issue.

This is a quick mockup of my case:

• Bills table has generic bill data
• Subtable 1 has Type 1 additional data
  • Any bill can have ONE Type 1 row
• Subtable 2 has Type 2 additional data
  • Any bill can have ONE Type 2 row

So a Bill can have both a Type 1 and Type 2, but only ONE of each.

Thus I cannot make any many-to-(any) relation, at least I dont see where to place it.

I dont want to make the generic bill table reference any Type X subtable as I will get more subtables over time and migrating a huge table in production is not to my liking.

Is there any ETA on a fix for this issue?

Currently my only option to overcome the issue is to first insert into generic bill and after that insert into each subtable manually using the UUID of the bill.

This by no means is my preferred fix as it causes more requests and code to do the same thing, if this where fixed.

[afitzek]

Is this still worked on? We are currently designing a new data base model for our cloud platform, and would like to use hasura, but this issue would be a bit of a set back, for our model.

[eviefp]

Fix is done and should land in master soon.

[followbl]

as someone has been using hasura for roughly ~2 years in production, this is definitely a candidate for least helpful error messages. super frustrating as a lot of things in hasura just work.

[corsali-albert]

@eviefp Is there a PR or commit I can track to see when this actually lands? I'm on 2-beta2 and still hitting this.

[dulara1994]

After further discussion with @rikinsk we finalised on having insertion_order when you are defining a manual object relationship.

{
  "type": "create_object_relationship",
  "args": {
    "table": "author",
    "name": "setting",
    "type": "one_to_one",
    "using": {
      "manual_configuration": {
        "remote_table": "author_setting",
        "column_mapping": {
          "id": "author_id"
        },
        "insertion_order": "after_parent"
      }
    }
  }
}

insertion_order can take two values, before_parent (default and the current behaviour) or after_parent which is needed for 'one-to-one' relationship inserts.

I changed, insertion_order: null to insertion_order: after_parent on the relevant table.yml file. worked for me,

[dalmo3]

How can I set insertion_order from the hasura UI?

[maaft]

.yaml file of the table (metadata). Default is insertion_order: null

[Zerebokep]

@rikinsk New relationship UI is still missing a insertion order dropdown.

[famu1hundred]

For anyone still struggling with this, I was able to get this to work in the UI by exporting the Metadata json file (gear in the top right hand corner of hasura UI), changing the relationships to "insertion_order": "after_parent", save and then Import the Metadata json back into the UI.