I want to know that what is the problem with the following code in the mysql:?

Question

I want to create a table named "user" in mysql with the follwing command :
mysql>CREATE TABLE user ( usernumber mediumint(10) DEFAULT '0' NOT NULL AUTO_INCREMENT , userid VARCHAR(8) BINARY NOT NULL, userpassword VARCHAR(20) BINARY NOT NULL, PRIMARY KEY (userid), UNIQUE usernumber (usernumber)); the error messege it is giving is ERROR 1067(42000): invalid default value for 'usernumber'

Answer 1

You have the default for usernumber in quotes which denotes it as a string. Try removing the single quotes from around the zero.

Answer 2

The error is very simple that ... if ur assiging a value to a field which is a string then it should be in (' ') where as if u r assigning a value for an integer type it should be without ('') .. i had Rewritten the code check this out ...

CREATE TABLE user ( usernumber mediumint(10) DEFAULT 0 NOT NULL AUTO_INCREMENT , userid VARCHAR(8) BINARY NOT NULL, userpassword VARCHAR(20) BINARY NOT NULL, PRIMARY KEY (userid), UNIQUE usernumber (usernumber))


Thanks
Suraj Sharma

Answer 3

usernumber mediumint(10) DEFAULT '0' NOT NULL AUTO_INCREMENT

AUTO_INCREMENT remplace the DEFAULT value
the first row insert will be set to DEFAULT '0'
the second row insert will be set to DEFAULT '1'
the third row insert will be set to DEFAULT '2'
......
you can't use DEFAULT and AUTO_INCREMENT in the same field
use this instead :

usernumber mediumint(10) NOT NULL AUTO_INCREMENT