MySQL Error Handling

[Medyman]

Hello O' MySQL Wise Ones...

I'm entering the following MySQL string into my databse to create a new table:

CREATE TABLE auth (
  userid
    int(4)
    unsigned
    zerofill
    DEFAULT '0000'
    NOT NULL
    auto_increment,
  username varchar(20),
  userpassword varchar(20),
  PRIMARY KEY (userid)
);

And I get this error when I try to do so:

#1067 - Invalid default value for 'userid'

I know nothing about MySQL so any help is appreciated. I got that MySQL query from an online tutorial.

Thanks a million!

[Twey]

'0000' is a string, not an integer. userid has been specified as an integer field. Just do:

    DEFAULT 0

[Medyman]

Thanks for the reply!

I did what you said but I'm still getting the same error. What else could be at issue?

[thetestingsite]

That's the only issue that it could be, if it is giving the exact same error. Make sure your query is like this:

CREATE TABLE auth (
  userid
    int(4)
    unsigned
    zerofill
    DEFAULT 0
    NOT NULL
    auto_increment,
  username varchar(20),
  userpassword varchar(20),
  PRIMARY KEY (userid)
);

If it is, make sure that you are in fact getting the same error message.

Hope this helps.

[Medyman]

I copied and pasted the code you provided...still getting the same EXACT error.

Here is a screenshot of the error page:

[mwinter]

You cannot have a default value: the column is auto-incremented.

[kamal_chandel]

Creating a new Table in MySQL
Following is the absolute minimum required to create a new table in MySQL:

CREATE TABLE tbl_name
[(create_definition, ...)] [table_options]
The complete syntax is pretty long actually and I'd suggest that you consult MySQL documentation for that. Here is the CREATE TABLE command that we are going to use to create a "users" table with 4 fields in the "examples" database we just created:

CREATE TABLE users (
user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name TINYTEXT NOT NULL,
last_name TINYTEXT NOT NULL,
country_code CHAR(2) NULL
);