CODEWITHSUNDEEP
phpmysqlsqlauto-increment
Lou
Lou

Reputation: 2509

Why does Auto_Increment return null?

I'm writing a web application that will insert new records into a MySQL database table. Most of the information is user-supplied, except for the primary key, which I want to obtain using Auto_Increment. When I run the following query from my web application:

SELECT Auto_Increment FROM information_schema.tables WHERE table_name='charlist';

It returns nothing - I checked by storing the result of the query in a variable and then echoing that variable, and nothing was output. I also used the MySQL console to run this query, and it returned this:

+----------------+
| Auto_Increment |
+----------------+
|           NULL |
+----------------+

My table definitely has a primary key field, and it has 5 records with the primary key field filled in. This is the structure of the table.

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id             | int(11)     | NO   | PRI | NULL    |       |
| bookIntroduced | int(11)     | YES  |     | NULL    |       |
| pageIntroduced | int(11)     | YES  |     | NULL    |       |
| title          | varchar(50) | YES  |     | NULL    |       |
| forename       | varchar(50) | YES  |     | NULL    |       |
| surname        | varchar(50) | YES  |     | NULL    |       |
| oldSurname     | varchar(50) | YES  |     | NULL    |       |
| alias          | varchar(50) | YES  |     | NULL    |       |
| regnalNumber   | varchar(5)  | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

And this is a few fields of the 5 records already in the table.

+----+-----------+---------+
| id | forename  | surname |
+----+-----------+---------+
|  1 | Waymar    | Royce   |
|  3 | Mance     | Rayder  |
|  4 | Gared     |         |
|  5 | Tristifer | Mudd    |
|  6 | Edric     | Dayne   |
+----+-----------+---------+

So why would Auto_Increment return NULL, when there are records in the table with a valid primary key?

Upvotes: 2

Views: 4683

Answers (2)

Shahzad Malik
Shahzad Malik

Reputation: 561

Your key is Primary Key but is not set as auto increment. Please alter your table like

ALTER TABLE `charlist`     CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

Upvotes: 5

ins0
ins0

Reputation: 3928

because the id column is not marked default as auto_increment.

Upvotes: 1

Related Questions