mySql database - ID auto_increment : what are the risks of it?

Question

I´m kind of new to databases and I was hoping you could answer my question: I have a table with an ID that´s a primary key, not null and Auto increments. It´s data type is int, with a limit of 50 digits. this table will be receiving new rows constantly, so I was wondering if it´s dangerous to leave as it is because of the digit limit and how can I override this problem. I know it might sound silly, but I´m really starting to know about databases.

Answer 1

The risks with auto_increment are not if you run out of numbers; you should set your data type appropriately and hitting the max value is not a an inherent risk but simply bad design. The inherent risks are something coming along and causing your IDs to ramp up unexpectedly. For example if there are UPDATE/REPLACE statements which can modify other fields it can cause a record to be replaced by an identical one with a new auto-incremented ID. For example if you have a column 'ID' which is auto_increment and you have another UNIQUE key in the table, a REPLACE statement can absolutely cause the value of ID to be set to the next available one. A lot of care has to be taken when putting auto_increment in a table when that table has many columns and indexes, in particular if it has other unique keys. I'd only recommend using auto_increment on tables which are extremely simple, like log-tables, where things are written once and then generally not updated much after. For any other table I'd recommend using a different system to generate IDs, such as MD5, UUID or natural keys if the data allows for those.

For auto-increment I'd never recommend INT, use INT UNSIGNED. Same for BIGINT, use UNSIGNED.

Answer 2

If you're concerned that it may hit the 50 digits (that's a lot of rows, I would really be surprised if you reached it), you could setup a scheduled job to monitor the max Id and when close to 50 digits, then send an email to system admins about the need to clean up space or increase field size.

As far as using auto numbers the only real problem you have over time will be spaces.. How do you clean up empty data and allow the system to re-use those numbers if reaching the limit is a concern?

A secondary concern is moving records into or around in test databases. Since the number will be automatically assigned, you either have to disable the auto number or make accommodations.

Answer 3

I dont think there is any danger which you will face with that. If you have set the primary key to contain maximum of 50 digits then I dont think there is any issue with that as

9999999..........50 times...99 will be a large number

However I am not sure which datatype you are using. Because as far as BIGINT is concerned a BIGINT is always 8 bytes and can store -9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned). which is no where near to 50 digits but yes it is a big number and very rare to reach.

On a side note:

[...] This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. ...

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

Answer 4

A field with a type of Int will have 2,147,483,647 numbers usable.

If you are worried about this, you can use the BIGINT field type.

That being said, using an Auto-incremented INT for your primary key is the industry standard.

http://www.w3schools.com/sql/sql_autoincrement.asp http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html