MySQL 5 -- "Invalid default value"?

Hi,

I'm trying to create a table in MySQL 5.0. The table created fine in
MySQL 4.0. The statement is

CREATE TABLE USER_GROUPS (
GROUP_ID INTEGER UNSIGNED DEFAULT 0 NOT NULL
AUTO_INCREMENT,
NAME VARCHAR(255),
CONSTRAINT PK_USER_GROUPS PRIMARY KEY (GROUP_ID)
) TYPE = INNODB;

and the error is

#1067 - Invalid default value for 'GROUP_ID'

What to do? Thanks, - Dave

Re: MySQL 5 -- "Invalid default value"?

On 2 Oct 2006 05:44:20 -0700, laredotornado@zipmail.com wrote:

>I'm trying to create a table in MySQL 5.0. The table created fine in
>MySQL 4.0. The statement is
>
>CREATE TABLE USER_GROUPS (
> GROUP_ID INTEGER UNSIGNED DEFAULT 0 NOT NULL
>AUTO_INCREMENT,
>
>and the error is
>
>#1067 - Invalid default value for 'GROUP_ID'

It makes no sense to have both a default and the auto_increment clause.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool