At 23:02 -0500 1/6/03, Matt Hargraves wrote:
Well, he can do it, he just can't do it the way that he's trying to do it.
He needs to do what the rest of the world does and separate out his
different fields into different tables for labeling things. If he needs to
make a different table for each 'room' or whatever, then so be it, he just
can't try doing it all in one table. He simply needs to go pick up some
books that explain what normalization is and apply those rules and he'll be
fine.
Well, either that, or just use MyISAM tables, which do support
composite-key, multiple-independent-sequences the way he wants. :-)
Matt
----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "vinita Vigine Murugiah" <[EMAIL PROTECTED]>; "kayamboo"
<[EMAIL PROTECTED]>; "mysql" <[EMAIL PROTECTED]>
Sent: Monday, January 06, 2003 10:49 PM
Subject: Re: auto increment
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
