Create table w/ DEFAULT setting problems

[Ryan]

I am having problems creating a new table with a default and auto
increment such as this...

CREATE TABLE `customer` (
`cust_id` INT( 5 ) DEFAULT '00000' NOT NULL AUTO_INCREMENT ,
`cust_name` VARCHAR( 15 ) NOT NULL ,
`cust_last` VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `cust_id` ) ,
INDEX ( `cust_last` ) ,
FULLTEXT (

) CHARACTER SET = latin1
The error mesg I get is:

MySQL said:

#1067 - Invalid default value for 'cust_id'
If I were to remove the auto increment attribute, it would get created
fine!?

Could anyone direct me to where I can find info on this, whethere it
is syntax related to a step I missed. I know in PostgreSQL, a
variable has to be set up as an incrementer, and then linked to the
field where it will be used. Do I need to do anything along those
lines...

Thankyou,
Ryan

[BDR]

Don't make yourself go nuts over this.... a correct INT is a value of 0.
not 000000. Besides that, your first insert of a new record will have
the value of 1, not 00001. If you want 5 digit reference numbers,
you'll have to use a function to pad your int value or switch to varchar
and do it in sw.

Ryan wrote:

I am having problems creating a new table with a default and auto
increment such as this...

CREATE TABLE `customer` (
`cust_id` INT( 5 ) DEFAULT '00000' NOT NULL AUTO_INCREMENT ,
`cust_name` VARCHAR( 15 ) NOT NULL ,
`cust_last` VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `cust_id` ) ,
INDEX ( `cust_last` ) ,
FULLTEXT (

) CHARACTER SET = latin1
The error mesg I get is:

MySQL said:

#1067 - Invalid default value for 'cust_id'
If I were to remove the auto increment attribute, it would get created
fine!?

Could anyone direct me to where I can find info on this, whethere it
is syntax related to a step I missed. I know in PostgreSQL, a
variable has to be set up as an incrementer, and then linked to the
field where it will be used. Do I need to do anything along those
lines...

Thankyou,
Ryan

[BDR]

Don't make yourself go nuts over this.... a correct INT is a value of 0.
not 000000. Besides that, your first insert of a new record will have
the value of 1, not 00001. If you want 5 digit reference numbers,
you'll have to use a function to pad your int value or switch to varchar
and do it in sw.

Ryan wrote:

I am having problems creating a new table with a default and auto
increment such as this...

CREATE TABLE `customer` (
`cust_id` INT( 5 ) DEFAULT '00000' NOT NULL AUTO_INCREMENT ,
`cust_name` VARCHAR( 15 ) NOT NULL ,
`cust_last` VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `cust_id` ) ,
INDEX ( `cust_last` ) ,
FULLTEXT (

) CHARACTER SET = latin1
The error mesg I get is:

MySQL said:

#1067 - Invalid default value for 'cust_id'
If I were to remove the auto increment attribute, it would get created
fine!?

Could anyone direct me to where I can find info on this, whethere it
is syntax related to a step I missed. I know in PostgreSQL, a
variable has to be set up as an incrementer, and then linked to the
field where it will be used. Do I need to do anything along those
lines...

Thankyou,
Ryan