[thelist] MySQL queries, quotes, and AUTO_INCREMENT

Matt Warden mwarden at gmail.com
Thu Apr 6 06:37:00 CDT 2006


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bernardo Escalona-Espinosa wrote:

> (B)
> 
> Second problem: has to do with deleting entries of a table when you
> have the primary key as AUTO_INCREMENT.
> 
> Seems that when deleting rows, the primary keys of the deleted rows
> aren't automatically re-used when new rows are inserted. This creates
> major chaos and confusion.

No, it would cause major chaos and confusion if they *were* reused,
especially since MySQL doesn't enforce referential integrity.

> I "shifted up" (or down, if you will) the
> remaining rows to close the gap manually. Then when i inserted new
> rows it continued with the old sequence numbering, leaving a gap
> again. This increased the already existing chaos and confusion.
> 
> I googled some and found that this behaviour depends on whether your
> tables are of the type BDB, MyISAM, or InnoDB. Unfortunately I have no
> idea what engine my server uses and have really no understanding of
> these db types all together.
> 
> Can anyone please enlighten me?

Why do the gaps bother you? auto_increment is only a way to ensure you
have a unique identifier. If you need something that has some semantics
behind it, might I suggest another field, e.g.:

id        int     auto_increment   pk
foo       varchar(255)
priority  int     not null

A warning: your shifting can cause some serious serious problems with
your data integrity. If you're going to do that, make sure you have
table locks first.

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFENP1crI3LObhzHRMRAgc9AJwISyh6lSzJEQDkQAuCoNE07bp0HgCg6YIw
18IoSAY1UakD+/DrPbKrzjw=
=Ittt
-----END PGP SIGNATURE-----



More information about the thelist mailing list