#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0

    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.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    Hi,

    explicitly list the columns:

    Code:
    INSERT INTO
    	piggy_bank (coin, coin_year)
    VALUES
    	('Q', 1950)
    ;
    Just like you do when you wanna use a DEFAULT rathern than your own value.

    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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    25
    Rep Power
    0
    AH ok I see. Thank you so much!

IMN logo majestic logo threadwatch logo seochat tools logo