4

In a game server code in Java, I split the client data into many tables, to ease adding new content in the future.

Right now I have the following tables

  • names
  • auth
  • inventory

The name table contains the columns id and name.

The auth table has columns id and password.

The id column on the auth and inventory tables have foreign key constraints on the name's id column.

When creating a new client, I insert into the name table, get the generated id and insert the rest of the data in the other tables in separate insert statements, but all in a single transaction.

The problem is, when I insert into the auth table, it fails because of the foreign key check in name because I haven't commited it yet. Is there a better way to solve this without commiting on every insert?

duellsy
  • 8,497
  • 2
  • 36
  • 60
Délisson Junio
  • 1,296
  • 4
  • 21
  • 43
  • 1
    It sounds like you are misdiagnosing the problem or your code isn't doing what you think it's doing. It would be absurd if you couldn't insert into the parent table and the child table within the same transaction... that's sort of the point of transactions, grouping database changes together into an atomic unit of work, all or nothing. – Michael - sqlbot Jan 02 '14 at 07:46

3 Answers3

5

Until and unless you rollback after you insert into the parent table the child table will allow to insert rows corresponding to the newly inserted row in the parent table even if you don't commit after inserting into the parent table. So it is hardly a matter of worry. The impact of the DML operation persists till the next rollback and becomes permanent with a commit statement. So if you don't rollback explicitly after the insert operation in the parent table the child table will always allow insert corresponding to all the rows in the parent table irrespective of any commit operation.

So the problem shouldn't even occur even if the FOREIGN_KEY_CHECKS=1.

Rajesh Paul
  • 6,793
  • 6
  • 40
  • 57
1

I did a bit of research on this issue to find out the solution. I had the same issue where a foreign key constraint was throwing an error when adding both the parent and child in the same transaction.

The issue and how to fix it is not with the code, but the database. The issue is deferrable constraints. The 2 links below discuss them for Oracle. The bottom 2 discuss them for SQLServer and MySQL. Some of my research showed that MySQL deferred automatically; the link below says it does not.

From what I am reading the constraint is normally tested immediately and fails. By deferring the constraint, it will test the completed commit.

http://docs.oracle.com/cd/E11882_01/server.112/e25789/datainte.htm#CNCPT88892
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c22integ.htm#4666
How to define a deferred constraint in MySQL
Does SQL Server allow constraint violations in a transaction as long as it's not committed yet?

Community
  • 1
  • 1
user3457436
  • 11
  • 1
-2

If you continue to have issues, as a temporary fix you can disable foreign key checks temporarily to get through

SET FOREIGN_KEY_CHECKS=0;

make sure to re-enable after with

SET FOREIGN_KEY_CHECKS=1;
duellsy
  • 8,497
  • 2
  • 36
  • 60
  • Can't I change my database structure to avoid this? Is it in the standars, at least? It just feels kinda hacky to bypass the check before commit, what if there is an actual fk constraint error, how would I deal with it? – Délisson Junio Jan 02 '14 at 04:42
  • unfortunately nope, that's the good with the bad. Foreign key checks are ruthless, the only way to insert a record with a foreign key that doesn't exist, is to not enforce the check. – duellsy Jan 02 '14 at 04:49
  • is there at least a way I could insert in all tables at once, in a way the checks would pass? – Délisson Junio Jan 02 '14 at 04:50
  • only if you don't to it in a transaction, or at least do a commit before you insert into the `auth` table. The `name` record must exist – duellsy Jan 02 '14 at 04:53
  • Nonsense. There is no need to commit against name before inserting in auth. Your transaction sees the new row you inserted, so, for you, the row *does* exist and the foreign key constraint is not violated. – Michael - sqlbot Jan 02 '14 at 07:51