How to have a auto increment field with default value of 1000

[Kajol]

Hi All,

I am using server version: 4.1.11-nt on windows 2000.

I want to create a table with ID as auto increment and initialise it
with a starting value of 1000.

I am trying to do following

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
id bigint not null auto_increment=1000,
`Author_Name` tinytext,
`Authors_Email` varchar(255) default '',
`Author_Password` varchar(8) default '',
`Author_Zipcode` int(6) default NULL,
`Author_DOB` date default NULL,
PRIMARY KEY (id)
) DEFAULT CHARSET=latin1;

The error i am getting is shown below

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that
corresponds to your MySQL server version for the right syntax to use
near '=1000
,
`Author_Name` tinytext,
`Authors_Email` varchar(255) default '',
`A' at line 2

From MySQL manual i learned that innoDB engine cannt have a default

value.

Please suggest me how to create a table so that it gets initialised by
1000 value (First record starts from 1000).

Thanks for your advices.

Regards,
Kajol

[Felix Geerinckx]

On 21/04/2005, Kajol wrote:

Please suggest me how to create a table so that it gets initialised by
1000 value (First record starts from 1000).

For MyISAM tables you can use:

CREATE TABLE foo (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = MyISAM AUTO_INCREMENT = 1000;

For InnoDB you need a workaround:

CREATE TABLE foo (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB;
INSERT INTO foo VALUES(999);
DELETE FROM foo WHERE id=999;

--
felix

--
felix

[Kajol]

Hello Sir,
Thank u so much.U solve my problem.

Regards,
Kajol