Auto_Increment Stating Number

am 21.07.2002 02:35:32 von PathFinder Software

What is the syntax for starting an Auto_Increment at a certain value like
1500 for example?

I have tried this string but MySQL gives me an error.

CREATE TABLE Test (TestID int not null auto_increment auto_increment =
1500,Year INT,Month INT,Day INT,Name VARCHAR(50));

Also can an auto_increment value contain characters like 1500AB?

Thanks,

Normand Charette
PathFinder Software
support@affiliatesoftware.net

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread1869@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Auto_Increment Stating Number

am 21.07.2002 04:32:44 von Garry Williams

On Sat, Jul 20, 2002 at 05:35:32PM -0700, PathFinder Software wrote:
> What is the syntax for starting an Auto_Increment at a certain value like
> 1500 for example?
>
> I have tried this string but MySQL gives me an error.
>
> CREATE TABLE Test (TestID int not null auto_increment auto_increment =
> 1500,Year INT,Month INT,Day INT,Name VARCHAR(50));

(An AUTO INCREMENT column *must* be a PRIMARY KEY.)

The syntax is defined in section 7.7 of the manual:

7.7 CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
...

An integer column may have the additional attribute
AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0
into an AUTO_INCREMENT column, the column is set to value+1, where
value is the largest value for the column currently in the table.
AUTO_INCREMENT sequences begin with 1.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

So what you are looking for doesn't exist.

You could insert a starting record in the table *after* creating it:

CREATE TABLE Test (
TestID INT AUTO_INCREMENT PRIMARY KEY,
Year INT,
Month INT,
Day INT,
Name VARCHAR(50)
);
INSERT INTO Test (TestID, Year, Month, Day, Name)
VALUES (1500, 1970, 1, 1, 'dummy');

> Also can an auto_increment value contain characters like 1500AB?

No. It must be an integer according to Section 7.7.

--
Garry Williams, Zvolve Systems, Inc., +1 770 551-4504

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread1870@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Auto_Increment Stating Number

am 21.07.2002 04:32:44 von Garry Williams

Re: Auto_Increment Stating Number

am 22.07.2002 16:05:21 von fision

You can specify the auto_increment start number, with the table creation
option auto_increment, like this:

create table test (
testid int not null auto_increment primary key,
year int
) auto_increment = 1500;

fision

On Sat, 2002-07-20 at 23:32, Garry Williams wrote:
> On Sat, Jul 20, 2002 at 05:35:32PM -0700, PathFinder Software wrote:
> > What is the syntax for starting an Auto_Increment at a certain value like
> > 1500 for example?
> >
> > I have tried this string but MySQL gives me an error.
> >
> > CREATE TABLE Test (TestID int not null auto_increment auto_increment =
> > 1500,Year INT,Month INT,Day INT,Name VARCHAR(50));
>
> (An AUTO INCREMENT column *must* be a PRIMARY KEY.)
>
> The syntax is defined in section 7.7 of the manual:
>
> 7.7 CREATE TABLE Syntax
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
> [table_options] [select_statement]
>
> create_definition:
> col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
> [PRIMARY KEY] [reference_definition]
> or PRIMARY KEY (index_col_name,...)
> ...
>
> An integer column may have the additional attribute
> AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0
> into an AUTO_INCREMENT column, the column is set to value+1, where
> value is the largest value for the column currently in the table.
> AUTO_INCREMENT sequences begin with 1.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> So what you are looking for doesn't exist.
>
> You could insert a starting record in the table *after* creating it:
>
> CREATE TABLE Test (
> TestID INT AUTO_INCREMENT PRIMARY KEY,
> Year INT,
> Month INT,
> Day INT,
> Name VARCHAR(50)
> );
> INSERT INTO Test (TestID, Year, Month, Day, Name)
> VALUES (1500, 1970, 1, 1, 'dummy');
>
> > Also can an auto_increment value contain characters like 1500AB?
>
> No. It must be an integer according to Section 7.7.
>
> --
> Garry Williams, Zvolve Systems, Inc., +1 770 551-4504
>
> ------------------------------------------------------------ ---------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail msql-mysql-modules-thread1870@lists.mysql.com
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.
>

------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail msql-mysql-modules-thread1873@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail msql-mysql-modules-unsubscribe@lists.mysql.com instead.

Re: Auto_Increment Stating Number

am 22.07.2002 16:05:21 von fision