[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