Asking me to specify autoincrement value when inserting

Databases
1

Why is mysql asking me specify value for column that is on autoincrement?

$sql = "CREATE TABLE IF NOT EXISTS My_Table (
            id INT NOT NULL AUTO_INCREMENT,
            name VARCHAR (10) NOT NULL,
            PRIMARY KEY (id)
        )";
insert into My_Table values ('John' ); //  doesnt work
insert into My_Table values (1, 'John' ); //does work

#1136 - Column count doesn’t match value count at row 1

3

You need to give the column to where you want the values to be inserted if you do not specify all columns. Otherwise MySQL will not know where to put them

INSERT INTO mytable (name) VALUES ('joe')
2 Likes
4

I was under the impression it looks at the order of values

5

In that case it would try to put your ’John’ into the id column what also would not work :slight_smile:

2 Likes
6

thank you

7

It does, but if you don’t specify the columns, it will assume the order of all the columns to match the order of the values given.
So if id is the first column and John is the first value you give it, “John” goes into “id”.
But if you specify name as the first column you want to populate, “John” goes to that. Unspecified columns get the default value, AI or other.

1 Like
8

Yes, But at the end I would never ever use a insert without specifying the columns. What if you change the database structure in the future to match a new function and you insert a column? All your code will not work. If you specify the column names in the insert, the new column is not filled, but the code still works fine.

Also if someone else needs to read your code. He needs to check the database structure every time he sees an insert in your code as he cannot know what you are inserting. What a mess……

2 Likes