ansaurus

Question

Answer 1

+1 A:

Use a foreign key constraint with 'SET NULL' for updates and deletes.

Mark Byers 2009-12-28 23:52:21

This is the problem. I copied and pasted the wrong thing, actually.

Timmy 2009-12-28 23:54:19

Is there a problem? The maximum value is quickly derived from your autoincrement field's *index*. There's no table scan involved.

Derek Illchuk 2009-12-28 23:56:26

I added a little more explanation up top, but the problem is that there are other tables referencing this table, using the id as a receipt. For those tables, this column is no longer unique.

Timmy 2009-12-28 23:57:59

I think you are misunderstanding the documentation. When you insert a row, the automatically generated id is saved to disk. The only thing that isn't saved to disk is the cache of what the next value should be, but this doesn't affect the functionality. It just gives a *very* minor performance hit on startup.

Mark Byers 2009-12-29 00:00:56

The problem the OP is having is this: insert a row with id=42 in a tableA. Insert another row with foreign key(42) into tableB. Delete id=42 from tableA. Now restart the server and insert another row in tableA. It will get id=42, and the referencing foreign key in tableB would now point to data that is really not related

nos 2009-12-29 01:05:08

@nos: OK, that makes sense. I couldn't work out what the problem was from the description in the question, but I see it now. The solutions is simple though: InnoDB supports foreign key constraints, so when you delete the row, you can make sure that no other rows are still pointing there. I've updated my answer.

Mark Byers 2009-12-29 01:17:54

Answer 2

+1 A:

So you have two tables:

TableA

A_ID [PK]

and

TableB

B_ID [PK]

A_ID [FK, TableA.A_ID]

And in TableB, the value of A_ID is not unique? Or is there a value in TableB.A_ID that is not in TableA.A_ID?

If you need the value of TableB.A_ID to be unique, then you need to add a UNIQUE constraint to that column.

Or am I still missing something?

iKnowKungFoo 2009-12-29 00:06:42

There is a unique constraint, which is how it's failing for me - it tries to insert into TableB an A_ID that already exists, because of the server restart and the AUTO_INCREMENT getting reset.

Timmy 2009-12-29 00:12:10

Answer 3

+1 A:

If you have a foreign key constraint, how can you delete a row from table A when table B references that row? That seems like an error to me.

Regardless, you can avoid the reuse of auto-increment values by resetting the offset when your application starts back up. Query for the maximum in all the tables that reference table A, then alter the table above that maximum, e.g. if the max is 989, use this:

alter table TableA auto_increment=999;

Also beware that different MySQL engines have different auto-increment behavior. This trick works for InnoDB.

Ken Fox 2009-12-29 01:32:49

Seems fair. I'm using it somewhat as a receipt, though maybe I should take another approach.

Timmy 2009-12-29 01:56:38

Answer 4

+1 A:

Create another table with a column that remembers the last created Id. This way you don't have to take care of the max values in new tables that have this as foreign key.

f3r3nc 2010-04-20 00:09:20

ansaurus

tags:

views:

answers:

Auto_increment values in InnoDB?

TableA

TableB

related questions