At 13:43 +0100 5/13/03, Tom Gazzini wrote:
>I'm trying to create a table which will form the basis of a database of
>items in a shop. Each item has a category id and an item number. The SQL
>I'm using is this:
>DROP TABLE IF EXISTS item;
>CREATE TABLE item
> cat_id TINYINT UNSIGNED NOT NULL,
> item_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> PRIMARY KEY(cat_id, item_id)
>) TYPE = MYISAM AUTO_INCREMENT = 100000;
>('cat_id' points to the primary key of another table called
>As you can see, I'm trying to make a composite primary key made of of
>(cat_id, item_id). I'm also trying to produce 6-digit item_id's by using
>the "AUTO_INCREMENT=100000" trick;
This doesn't work for composite keys that include an AUTO_INCREMENT
column, I believe.
>However, if I test this using the following SQL:
>INSERT INTO item (cat_id) VALUES (1);
>the item_id does starts from 1, instead from 100000.
>Can anyone see any reason why this is happening?