List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 7 2003 4:07am
Subject:Re: auto increment
View as plain text  
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
>  > >>>>

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