CODEWITHSUNDEEP
mysqlsql
user1427661
user1427661

Reputation: 11784

Inserting New Items Into an Already Populated Table

I have a table with various fields including a primary key, id, which is auto-incrementing:

+-------------------------------+------------------+------+-----+---------+
| Field                         | Type             | Null | Key | Default | Extra
+-------------------------------+------------------+------+-----+---------+
| id                              | tinyint(11)      | NO | PRI | NULL   | auto_increment

The table is already populated with 114 items:

mysql> select count(*) as cnt from beer;
+-----+
| cnt |
+-----+
| 114 | 
+-----+

And I am trying to insert a group of new items into the table. I am not explicitly inserting an id key. Here's a sample query:

mysql> INSERT INTO beer (name, type, alcohol_by_volume, description, image_url) 
    VALUES('Test Ale', 1, '4.6', '', 'https://untappd.s3.amazonaws.com/site/assets/images/temp/badge-beer-default.png');

I get the following error when attempting to manually insert that query (the insertion is actually done with a PHP script to the same results): 

ERROR 1062 (23000): Duplicate entry '127' for key 1

What's going on? I thought the id would automatically increment upon insertion. I should note that the first 13 entries are blank/null for some reason, and the last key is currently 127. (it's not my table -- I'm just writing the script).

Upvotes: 0

Views: 74

Answers (2)

Pooyan Arab
Pooyan Arab

Reputation: 136

Try resetting the auto increment of primary key manually using this:

ALTER TABLE `beer` AUTO_INCREMENT = 128;

Upvotes: 1

a1ex07
a1ex07

Reputation: 37384

Tiny int is not the good choice for auto_increment primary key... Range is just (-128...127). Normally it's used as a flag; you need to use unsigned int

Upvotes: 4

Related Questions