auto_increment problem

[Jppr]

Hi,

I work with MySql, and I have a field auID, which has option auto_increment. This works ok, when I add new entries.
But when I delete an entry (say nr. 4), and then add a new one MySql starts counting from 5, instead of 4 again.

What do I have to do in order to let MySql count right?
Thanks

Thomas

[Robo]

That's suppose to happen, think of the IDs as "disposible", use it once, and throw it away.

The reason being, say in a news submittion script, author Bob has the ID 2, and in another table, the news content links to ID 2, which tells the news that it was posted by Bob, ok?

Well if you remove Bob from the db, and added a new user Pete, and like you said, was given the ID 2 again, does that mean the all the news previously submitted by Bob, now belongs to Pete?

[Jppr]

Thanks for the quick reply.

Now suppose that I know for sure that Bob don't has any articles to his name yet, do I need to manually change the values for auID ?

other question: in the management system I made with PHP for the authors, I need something to turn a "" (empty) value to a NULL value, so that MySql can throw an error message if that field is required.
Any ideas?

Thomas

[Robo]

If you really want to change the auID, I guess you would have to do it manually, using the update function.

UPDATE [dbname] SET auID='4' WHERE name='Pete'

If a field is required, you set that field to NOT NULL, so if that field is empty MySQL will complain.

If it's in a form, you can verify that the field has something in it before submitting.

if ($submit)
{
   if (!$fieldname)
   {
   echo "<p>Hey, you skipped a few fields, don't be so lazy</p>"
   }

else
   {
   // Process Form
   }
}

[freddydoesphp]

an auto_increment field is and always should be a simple unique number that corresponds to the record, it shouldn't matter if you skip some id in the sequence. It SHOULD NOT be used as a sequential number of records for counting the total number of records there are functions for that like count(*).

[Jppr]

Is there no special function to change empty "" to unknown NULL then (in php)

Or $null or something?

[freddydoesphp]

Not sure I follow you your php script should dictate was is required or not. You should be doing your error checking you script not letting mysql give the error if smething is required.

[Kevin Yank]

I think what Jppr wants is for his PHP script to insert a NULL entry when the user doesn't enter anything in the text field (producing a variable with value "").

Here's how I do it:

// Add quotes or set to NULL.
$var = ($var == "") ? "NULL" : "'$var'";

...

// SQL Query
$sql = "... SET ColName=$var, ...";

[Son Nguyen]

Just a question about this auto_increment:
what if the field keeps increasing and doesn't fit into that field's type? (say TINYINT for some index over 256)
Would the number be cut off? And would it ruin what the auto_increment field should be?

[Robo]

If the number doesn't fit, MySQL automatically change the type to the next one up, eg, INT.

Same applies the other way round, say you set a type to be VARCHAR(20), and all your data are under 4 characters, it'll automatically change to CHAR.

[Son Nguyen]

I don't think it will automatically push to the next bigger type. Since when I assign a field INT, then I put a big number (out of its range), it got cut off!
But with CHAR and VARCHAR, I agree that how it should work!

[Jppr]

Thanks for all of your replies.

I included a security for empty fields now.

About the auto_increment issue: I can manually set the id to something, but what if I want to reset the auto_increment value.

I found in the manual that myisamchk has an option set-auto-increment[=value] but I'm not sure how this works.
Alternatively, is that auto_increment value stored somewhere in a db or something? (in "mysql" maybe?)

Thomas

[Kevin Yank]

Why would you want to reset the auto increment value? For a good database design, no useful information should be stored in the autoincrementing column. It should serve only as a unique identifier for each item in the table. Thus, it shouldn't matter what the actual value is, as long as it's unique.

[Son Nguyen]

Kevin,
As you said, does that column help with indexing? Not really in my understanding, since you said that field shouldn't contain any special data we want at all, so when fetching data, we probably don't need it.

So what are the advantages to have a unique ID for every row?
Thanks

[Kevin Yank]

First of all, it provides a unique identifier for each row (avoids problems like two people named "John Smith" being indistinguishable in your database). The ID should also be used when associating that row with related information in other tables. This is all covered in the chapter on database design in my PHP/MySQL tutorial.

[Jppr]

Well,

I did read your tutorials Kevin, and I did learn a lot of it.

Indeed, the column with auto_increment is used as identifier for articles, authors and categories. It's just a number.
As you know I'm building a management system to manage all the articles etc. But to check if everything works the way I want, I inserted some test values, and afterwards deleted them.

Now the management system works, I insert for example two authors, but they already start with id 9 en 10. That's what I'm bothered about, because I used tinyint, and it can have 255 in unsigned mode. Suppose I delete a lot of entries then the id's will rise high and I would have to change the field type.

So I wanted something to reset it.

An option I found is to use
shell> mysql < tablebackup.sql

and inside tablebackup the queries to drop the table, and then re-create and re-populate it. But I think that's a bit drastically, because suppose I forgot to backup before I dropped the table...

Thomas

[Kevin Yank]

Indeed, if MySQL gets to the highest allowable value in a column you won't be able to insert any more items in the table (MySQL will complain of duplicate values).

My first recommendation would be to change the column type. If you're worried about running out of IDs, then you're definitely using too small a column type. TINYINT UNSIGNED (256 keys) requires 1 byte of storage, SMALLINT UNSIGNED (65536 keys) requires 2 bytes of storage.

Are you really going to waste time worrying about this problem for the sake of 1 byte per author?

If you can bare 4 bytes of storage, you can use INT UNSIGNED and have 4294967296 keys!

Changing the column type non-destructively is easy to do with an ALTER TABLE command -- let me know if you need help with this.

[Robo]

If the ID was real money, then I'll be worry.

But it's only a number, it's disposible, it doesn't even get display on the webpage, so you really shouldn't care if a few numbers doesn't get use. You're not wasting anything.

Just alter that column to SMALLINT, or INT, and you'll be set.

[Jppr]

Okay, I'll take that extra byte then

Thanks for the replies everybody.

Cheers,
Thomas