Re: auto increment

[vinita Vigine Murugiah]

Paul DuBois wrote:

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. :-)

Thanks for the info!! The reason why I'm using INNODB table is to support the FOREIGN key & to have the transaction safe tables.
well..... I was bit surprised to see that it does NOT support the multiple-independent-sequencing. Say for Eg I have a table for supplier which has info on their ID, Name, accountNum, URL etc...)
Then I have another table called supplier_address because one supplier could have more than one mailing address.
+------------+-----------------+
| Field | Type |
+------------+-----------------+
| supplierID | varchar(20) |
| address | blob |
+--------------+----------------+

In this case ( I guess I can't split the table any more ) inorder to uniquely identify the entry I have to use the multiple-independent-auto increment. I can't use address as part of the primary keys, it doesn't have the Characteristic to use as part of the primary key(blob). Well..... If mysql doesn't support this, then i need to change the design a bit.

Thanks

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

 > >>>>

--


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