Xata•11mo ago

Diagnosing Constraint Violation

Hi team! We are using a direct SQL to update rows in one of our databases. Some of them work, some of them throw a "constraint violation on transactions: link_m29iv801n56pd8oc7tk3q9nn2c" The requestId is "32375456-2959-9b47-925b-4f8fcbbaac14" This gotta be something wrong in our code, but I am failing to see what, so we need some support from you to see what's wrong.

11 Replies

Eusebio Trigo•11mo ago

Got a similar message using the playground.

kostas•11mo ago

Hey! The column link_ji0jbialgp7ml4heg4hf9s5ius (shop) in transactions is a link to the table sellers. This reads like the request is trying to a create or update a record in table transactions setting a shop id that does not exist in sellers.

Eusebio Trigo•11mo ago

Wow, interesting!

UPDATE transactions
        SET report = transactions.credential_id || '_' || orders.ship_to_country_iso_code || '_' ||
                     EXTRACT(YEAR FROM transactions.accounting_document_creation_date)
        FROM orders orders
        WHERE transactions.order_id = orders.order_id
          AND transactions.report IS NULL
          AND transactions.credential_id = ${envId}

UPDATE transactions
        SET report = transactions.credential_id || '_' || orders.ship_to_country_iso_code || '_' ||
                     EXTRACT(YEAR FROM transactions.accounting_document_creation_date)
        FROM orders orders
        WHERE transactions.order_id = orders.order_id
          AND transactions.report IS NULL
          AND transactions.credential_id = ${envId}

The query does not use anything from shops/sellers. I'll go through that path! report, which is also a linked table is related to shops as well, same as orders. Why would the query update anything to the shop.id column / shop link? Thanks for checking it, Kostas, you are a 🌟 And why some of the queries worked and some didn't 😦

kostas•11mo ago

Indeed this query should not try to update the seller column. In your workspace configuration there is an option Allow support to view your workspace , if you flick that on I will be able to access your workspace and try to figure out what's going on (without editing your data, obviously)

Eusebio Trigo•11mo ago

Added. I would be the one editing data, 😉 , hopefully!

kostas•11mo ago

Hah right that's the goal at least. Thank you, I'll have a look The statement in question errors on a different column: "reports", not "sellers". Perhaps, if you run more than one query the error responses weren't paired correctly with the queries? The issue here is that the report column in table transactions is a link to the reports table. So in order to set a value there, the reports table must have a record with that id. Your statement generates some record ids that do not exist in table reports:

rec_cl2c9gvnggvk5u5em6mg_CYP_2023
rec_cl2c9gvnggvk5u5em6mg_DEU_2023
rec_cl2c9gvnggvk5u5em6mg_FIN_2023

rec_cl2c9gvnggvk5u5em6mg_CYP_2023
rec_cl2c9gvnggvk5u5em6mg_DEU_2023
rec_cl2c9gvnggvk5u5em6mg_FIN_2023

So when trying to set those in the "report" value in "transactions", it triggers a constraint violation - because the target record doesn't exist. Here's how I went about investigating it: I took a snapshot of your database with our tool xreplay https://github.com/xataio/xtools/tree/main/xreplay so that I don't mess around with your actual database. I added a column "test" in the table "transactions". I run your statement, changing it to write outputs to "test" instead of "report" so that I can see the values it creates. I exported the reports table and the transactions table to CSV from Actions -> Export full table to CSV I compared (sort, uniq, then diff -y) the record ids of the "reports" table and the "test" column, spotting the excess ids in "test" which don't exist in "reports". Let me know if this makes sense or if there's anything I can do to help!

Eusebio Trigo•11mo ago

Yes, it makes sense! I'll need to go over the data that we have and see why those report ids are generated or why they haven't. Thank you for taking your time investigating it!

kostas•11mo ago

Of course, let me know how it goes and if we can help in any way!

Eusebio Trigo•11mo ago

I was reviewing the schema, and we may have too many linked tables anyway. Transactions does not need seller/shop, as it can be retrieved through the orders, etc... will think about it. You have been of great help, as always! Thank you!

kostas•11mo ago

We've noticed that linking tables tends to get out of hand in complex schemas. We're thinking how we can make joins simpler, so expect news on this front. But yes for now, the fewer link dependencies you can do with, the better.

Eusebio Trigo•11mo ago

TBH, I like the way it is now, very object oriented. Me likes it.

Gaming

Programming

Diagnosing Constraint Violation