0

I'm having some trouble with inserting some data into a SQL Server table using Hibernate. I have a table, and I have a FK from the table to itself to establish that entities in this table are related to one another. The PK for the table is type uniqueidentifier and the FK is also type uniqueidentifier, represented in my Java code by UUIDs.

The problem is, when I try to insert an object into my table with one of these FKs set, I get the following error:

java.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint <FK_NAME>. The conflict occurred in database <DB_NAME>, table <TABLE_NAME>, column <PK_NAME>.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727) ~[jtds-1.3.1.jar:1.3.1]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_172]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_172]

However, I've tried running an INSERT statement on the DB directly using SQL Server Management Studio using the exact same parameters, and the INSERT statement works fine. I've also traced my code in the debugger and the debug trace shows the UUID id being parsed from String to UUID correctly. I copy-pasted the value directly from the PK field in my database as well to make sure there are no typos.

I feel like I've tried everything but I have no idea why I'm getting this error. Can anyone help me out? Thanks.

Ertai87
  • 1,156
  • 1
  • 15
  • 26
  • Next, I would use SQL Profiler (or an Extended Events Session) to trace the sequence of calls made from your App to SQL while reproducing this issue. – David Browne - Microsoft Jul 25 '18 at 15:17
  • Sounds goofy since it works directly with SQL Studio. Normally if it were a `trigger` causing this problem it would happen there too (which was my first instinct). Triple check that you are in fact connecting to the server/db/table you think you are. Put a change into that table using Studio, and select it out using your connection string. Really feels like a problem like that. – sniperd Jul 25 '18 at 15:22
  • @sniperd If I don't include that FK field (it's a nullable field) then the request works perfectly (it inserts the correct data in the correct table in the correct database). – Ertai87 Jul 25 '18 at 15:33
  • The only thing I can think of next is if there is in fact an `instead of trigger` or something very odd going on. It really sounds like you've done everything right. – sniperd Jul 25 '18 at 17:40

1 Answers1

0

I figured it out. Turns out the UUIDs were getting corrupted somehow in the transmission to the database because I was using a deprecated method for writing using Hibernate. The UUIDs being written to the DB (both for my PK field and my FK field) were corrupted, so they didn't match. I changed my implementation to use the one described in this SO question and then everything worked.

Ertai87
  • 1,156
  • 1
  • 15
  • 26