CODEWITHSUNDEEP
mysqldatabasedatabase-design
bass71982
bass71982

Reputation: 75

Concerns using an auto increment field as a unique identifier in MYSQL

I am using a varchar random generated string through PHP as the unique identifer at the moment in my tables but after researching on this site, it seems it might be better to use an auto increment primary key field as the unique identifier due to the smaller size integer speed difference plus I wont have to worry about generating the random string.

My friend however has warned about doing this because of the potential for the increment number to change either to 0 if the index is lost or the increment number is reset for any reason. In addition if creating a copy of the database eg. for testing and COPY SELECT is used, the index is not created and the auto increment is lost.

Are these concerns valid? Should I just make the change? :)

Edit 1

I would use the auto increment field as the primary key and use it as the foreign key in other tables so my concern is if for any reason the auto increment primary key in the main table changes then the foreign key in the other tables would become invalid

Upvotes: 0

Views: 183

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191729

Just make the change.

  • auto_increment must be a primary key. This means that duplicate values are not allowed. Typically you should use int unsigned not null auto_increment primary key for the definition. No need for negative numbers.
  • This is a transparent identifier, so there should be no reference to its actual value anywhere. It may be convenient to reference the actual value for maintenance, but there shouldn't be any concerns about the value changing if the data is copied to a new DB. You can INSERT a value to that column to whatever you want as long as it does not already exist.

Upvotes: 1

Related Questions