April 3rd, 2013, 06:32 PM
Having trouble with AUTO_INCREMENT for id
Hey guys, I've been having trouble recently with primary key columns and specifically what to input.
CREATE TABLE piggy_bank
(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, coin CHAR(1) NOT NULL, coin_year CHAR(4));
When I start to input values, I get an error.
mysql> INSERT INTO piggy_bank VALUES ('Q', 1950);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO piggy_bank VALUES (' ', 'Q', 1950);
ERROR 1366 (HY000): Incorrect integer value: ' ' for column 'id' at row 1
mysql> INSERT INTO piggy_bank VALUES ('', 'Q', 1950);
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
I always thought it was '' or ' ' that's supposed to be entered first, for the id but it doesn't appear to work that way. It used to work which means I'm missing something.
April 3rd, 2013, 06:42 PM
explicitly list the columns:
Just like you do when you wanna use a DEFAULT rathern than your own value.
piggy_bank (coin, coin_year)
Listing the columns is good practice, anyway, because you actually see what goes where. Otherwise, the whole meaning of the query depends on some obscure internal column order.
April 3rd, 2013, 07:14 PM
AH ok I see. Thank you so much!