CODEWITHSUNDEEP
mysqlsqldatabase
Anakela
Anakela

Reputation: 65

Setting AUTO_INCREMENT and getting it to automatically give values in INSERT commands?

I have been having some difficulty trying to auto increment my ID columns in my MySQL database. The primary keys for each table have already been assigned, so now I am trying to make them auto incremented. I have tried the following with no success:

ALTER TABLE table_name MODIFY COLUMN column_name INT NOT NULL AUTO_INCREMENT;

ALTER TABLE table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT;

ALTER TABLE table_name CHANGE column_name INT NOT NULL AUTO_INCREMENT;

ALTER TABLE database.table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT;

I get a message saying:

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

BUT when I try to add values using INSERT INTO table VALUES ('value1', 'value2'); without entering a value for the primary key column, which is listed before the value1 column, MySQL gives me an error:

Error Code 1136. Column count doesn't match value count at row 1

I thought that once I set up auto increment, I would not have to put in a value for the auto incremented column. What am I doing wrong? :(

Upvotes: 1

Views: 51

Answers (1)

Mohamed Yasin
Mohamed Yasin

Reputation: 440

put NULL for the auto increment column in the INSERT statement. OR Change the insert query to:

INSERT INTO table (column1,column2) VALUES ('value1', 'value2');

For the insert statement if the columns are not specified before "VALUES" then mysql expects values for all columns. NULL is allowed for auto increment columns. Mysql ignores the NULL value and sets the auto increment value accordingly.

Upvotes: 2

Related Questions