Mysql Delete

[Dragonkai]

Is it possible to stop the increment of the id when deleting rows, even though you still need an auto increment id?

[sstalder]

What do you mean? Can you give an example?

[chazzy]

Is it possible to stop the increment of the id when deleting rows, even though you still need an auto increment id?

Are you using mysql? If so you can use the alter table statement, and reset the auto increment id.

Are you using oracle? If so you can reinitialize the sequence.

Don't know about others. But honestly, you shouldn't. id's are meant to be immutable. If you're deleting them, and someone references it as a new value, it could make things worse.

[Dragonkai]

Ahh, so does that mean...
(I am using Mysql)

What happens, when I delete a row that has a primary id. And there are other foreign id that reference to it. Or what should happen? Like I looked at something called ON DELETE CASCADE... What is that for?

Also BTW for Mysql, what is the Alter command to reinitialize ids. This is only for development purposes of course.

[chazzy]

ALTER TABLE t2 AUTO_INCREMENT = value;

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

You can use CASCADE ON DELETE or UPDATE ON DELETE or there's a few others, but I would think that you need to do something application side as well. Perhaps prompt the user?

[Dragonkai]

Thanks.