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" <paul@stripped>
>To: "vinita Vigine Murugiah" <vinita@stripped>; "kayamboo"
><kayamboosuresh@stripped>; "mysql" <mysql@stripped>
>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" <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
> > >>>>
