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" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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 : [EMAIL PROTECTED] Ph : (03) 8344 1273
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
