List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 7 2003 3:49am
Subject:Re: auto increment
View as plain text  
At 14:34 +1100 1/7/03, vinita Vigine Murugiah wrote:
>HI
>
>kayamboo wrote:
>
>>sql, query,
>>
>>OfCourse that is the default criterian for an auto increment column.
>>It need to be the primary key
>>
>ID is part of the Primary key, It works fine for other table type.
>I do *NOT* want
>+-----------+-----+-----------+
>| roomNum | id | lockNum |
>+-----------+------+---------+
>| 3.1         | 01  | 3            |
>| 3.1         | 02  | 5           |
>| 3.2         | 03  | 8           |        ====>> ID is 3
>+-----------+-----+----------+
>
>BUT I want to see
>
>+-----------+-----+-----------+
>| roomNum | id | lockNum |
>+-----------+------+---------+
>| 3.1         | 01  | 3            |
>| 3.1         | 02  | 5           |
>| 3.2         | 01  | 8           |        ==>>> ID is one
>+-----------+-----+----------+

Sorry.  InnoDB tables do not support the use of composite (multiple-column)
indexes to generate multiple independent sequences within a table this
way.

You cannot achieve the behavior you want with InnoDB.

>
>>
>>
>>But you can try a simple script to achieve this and declare your *id* as INT
>>column rather an auto_increment
>>
>What do you mean? I want the ID to be incremented automatically!!
>
>>
>>
>>
>>regards
>>
>>
>>
>>----- Original Message -----
>>From: "vinita Vigine Murugiah" <vinita@stripped>
>>Cc: <mysql@stripped>
>>Sent: Tuesday, January 07, 2003 11:35 AM
>>Subject: Re: auto increment
>>
>>>>HI ALL
>>>>    I couldn't have an auto-increment in ver 3.23.53 for the table
>>>>INNODB,
>>>>
>>>>
>>>>mysql> CREATE TABLE roomLockAuto (
>>>>    -> roomNum CHAR(20) NOT NULL,
>>>>    -> id INT(2) unsigned zerofill NOT NULL auto_increment,
>>>>    -> lockNum CHAR(20) NOT NULL,
>>>>    -> PRIMARY KEY (roomNum, id)
>>>>    -> ) TYPE=INNODB;
>>>>ERROR 1075: Incorrect table definition; There can only be one auto
>>>>column and it must be defined as a key
>>>>mysql> CREATE TABLE roomLockAuto (
>>>>    -> roomNum CHAR(20) NOT NULL,
>>>>    -> id INT(2) unsigned zerofill NOT NULL auto_increment,
>>>>    -> lockNum CHAR(20) NOT NULL,
>>>>    -> PRIMARY KEY (roomNum, id)
>>>>    -> ) ;
>>>>Query OK, 0 rows affected (0.01 sec)
>>>>
>>>--------------------------------------------------------------------------
>>>
>>----------------
>>
>>>>BUT it works if I do
>>>>mysql> CREATE TABLE roomLockAuto (
>>>>    -> roomNum CHAR(20) NOT NULL,
>>>>    -> id INT(2) unsigned zerofill NOT NULL auto_increment,
>>>>    -> lockNum CHAR(20) NOT NULL,
>>>>    ->  PRIMARY KEY (id)
>>>>    -> ) TYPE=INNODB;
>>>>Query OK, 0 rows affected (0.00 sec)
>>>>
>>>--------------------------------------------------------------------------
>>>
>>-------------
>>
>>>>BUT I really  want PRIMARY KEY (roomNum, id)
>>>>Any idea???
>>>>
>>>>--
>>>>
>>>>
>>>>warm regards
>>>>Vinita Vigine Murugiah
>>>>Email : vinita@stripped Ph : (03) 8344 1273
>>>>
Thread
Re: auto incrementvinita Vigine Murugiah7 Jan
Re: auto incrementvinita Vigine Murugiah7 Jan
  • Re: auto incrementPaul DuBois7 Jan
Re: auto incrementMatt Hargraves7 Jan
  • Re: auto incrementPaul DuBois7 Jan
Re: auto incrementvinita Vigine Murugiah7 Jan