unique auto_increment of product_id over 2 tables

[u01jmg3]

Below is the code so far for my 2 tables including some data which works fine but what I want to know is how I get the auto_increment of earrings(product_id) to respect necklaces(product_id) and vice versa such that product_id is unique over both tables???


CREATE TABLE earrings (
product_id int(5) unsigned zerofill NOT NULL auto_increment,
description varchar(100) NOT NULL,
metal varchar(50) NOT NULL,
size varchar(50) NOT NULL default '0cm',
colours varchar(50) NOT NULL,
price decimal(5,2) NOT NULL default '0.00',
category varchar(25) NOT NULL,
UNIQUE KEY product_id (product_id)
);

CREATE TABLE necklaces (
product_id int(5) unsigned zerofill NOT NULL auto_increment,
description varchar(100) NOT NULL,
type_of_fastener varchar(50) NOT NULL,
length varchar(50) NOT NULL default '0cm',
colours varchar(50) NOT NULL,
price decimal(5,2) NOT NULL default '0.00',
category varchar(25) NOT NULL,
UNIQUE KEY product_id (product_id)
);

INSERT INTO earrings (product_id, description, metal, size, colours, price, category) VALUES
('00001', 'Push through earrings with glass beads for pierced ears.', 'Sterling silver.', '2cm drop.', 'Pink/blue.', '4.50', 'earrings'),
('00002', 'Hoop earrings with wooden beads for pierced ears.', 'Niobium (hypoallergenic copper lookalike).', '3cm hoop.', 'Blue/copper.', '5.00', 'earrings');

INSERT INTO necklaces (product_id, description, type_of_fastener, length, colours, price, category) VALUES
('00003', '2cm amethyst donut pendant on satin cord with glass and silver beads and Chinese knotting.', 'Sliding knot fastener.', 'Length adjustable 30-40cm.', 'Purple/green.', '15.00', 'necklaces'),
('00004', 'Fused glass bead with copper and glass beads on a leather thong.', 'Copper barrel fastener.', 'Length 30cm.', 'Pink/orange.', '12.00', 'necklaces');

[aj_nsc]

Hmm....why would you need that situation? Give us some background...there might be another way.

I mean, technically it wouldn't be that hard. Every time you insert a row into one table, you just grab that id and set the auto_increment of the other table to 1+ that number....but again, give us background.

[u01jmg3]

There's not much background to this other than a product_id should be unique and it would be easier if it was automatically generated so for those 2 reasons I posted here. Inserting new data is currently done via PhpMyAdmin though so unless I made a page and used PHP to do what you suggest is there anyway using MySQL for this to be done? Not a big issue but I thought it could be done with MySQL and some fancy syntax.

Thanks.

[chazzy]

if you have to keep each type of product in a separate table (which I wouldn't recommend at all), you should at least define a separate table, say products, that just has a product_id that's auto incremented. you can get a reference back to it via last_insert_id() which you can use to insert into the various product tables (as a foreign key).

[NogDog]

I would probably make one "product" table with the fields common to all products, including an auto-incremented primary key product_id field. Then you could have separate tables for the data unique to different product types, using the LAST_INSERT_ID() function to get the relevant product_id:

CREATE TABLE products (
product_id int(5) unsigned zerofill NOT NULL auto_increment,
description varchar(100) NOT NULL,
colours varchar(50) NOT NULL,
price decimal(5,2) NOT NULL default '0.00',
category varchar(25) NOT NULL,
PRIMARY KEY product_id (product_id)
);

CREATE TABLE earrings (
earring_id int(5) unsigned zerofill NOT NULL auto_increment,
product_id int(5) unsigned zerofill NOT NULL,
metal varchar(50) NOT NULL,
size varchar(50) NOT NULL default '0cm',
PRIMARY KEY earring_id (earring_id)
);

CREATE TABLE necklaces (
necklace_id int(5) unsigned zerofill NOT NULL auto_increment,
product_id int(5) unsigned zerofill NOT NULL,
type_of_fastener varchar(50) NOT NULL,
length varchar(50) NOT NULL default '0cm',
PRIMARY KEY necklace_id (necklace_id)
);

INSERT INTO products (description, colours, price, category) VALUES
('Push through earrings with glass beads for pierced ears.', 'Pink/blue.', '4.50', 'earrings');

INSERT INTO earrings (product_id, metal, size) VALUES
(LAST_INSERT_ID(), 'Sterling silver.', '2cm drop.');

[aj_nsc]

Well, if a product_id must be unique, i think the easiest solution to this problem would be the addition of a third, very simple table, call it products. It would be like this:


table - products

product_id | type | id
0001 earrings 0001
0002 necklace 0001
0003 earrings 0002


The id field in this table will point to a product id in a table given by the type field in this main 'products' table.

That way you can keep your other two tables the way they are and just let their primary keys be normal auto increments.

It will take two queries everytime you add a product, one to the actual table (i.e. either earrings or necklaces) and one to the main 'products' table, but, as I see it, this setup gives you the most plasticity and also conforms to the criteria of each product have a unique id.