CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
dabockster
dabockster

Reputation: 1

Auto Incrementing User ID number in SQL table

I am trying to set up a database of users for an app I'm developing. Each user is specified by an ID number. Right now, I have it set up to auto increment to make things easy. However, I have the ID set as an INT right now. The issue is that the INT data type has a max of 255 fields. That is, I can only have 255 users in the current table.

How can I get around this limit? Or would I need to create a new table each time this 255 limit is reached?

Upvotes: 0

Views: 1044

Answers (2)

Sujith Royal
Sujith Royal

Reputation: 822

ALTER TABLE table_name ADD id INT PRIMARY KEY AUTO_INCREMENT;

Should work, as the range of int is -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

Upvotes: 0

Shiva
Shiva

Reputation: 6885

Use a tool like PHPMyAdmin or Navicat, and modify the table and change the field to INT or BIGINT

NOTE: From your question it feels like that you are actually using TINYINT and not INT as the range of INT in MySQL is from -2147483648 to 2147483647 refer to this post for more information.

Or Alternatly you can run this SQL Query to alter the table directly

ALTER TABLE tablename CHANGE oldCollumnName newCollumnName INTEGER; // or to any other type you prefer

The oldCollumnName newCollumnName values can be the same but still you need to specify it twice

Documentaion and more information for the above query can be found here

Update 1

As mentioned in the comment by Andriy , to avoid repetition of the column name you can also use the ALTER... MODIFY command, the SQL for it will be something like this

ALTER TABLE  `tableName` MODIFY  `columnName` INTEGER

Upvotes: 2

Related Questions