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
>>>>
