I would like to be able to avoid inserting a duplicate record. The duplication consists of 3 columns though.
Here is the scenario. I have this existing table item (which is a product item) CREATE TABLE ITEM ( parent_id INT NOT NULL default 0, id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, .... link_name varchar(50) ) TYPE=MyISAM; Now I want to create a table where I will put the item.id as a foreign key here into the item_meta_ref.item_id. The idea is for each item, I am going to allow a 1 to many set of metas. So as you can tell, the key(item_id) references item(id) on delete cascade, should work. Now the problem I want handle is the following: I want a unique combination of item_id, meta_id, and sort to be unique. Meaning, I want to throw an error if someone tries to insert a duplicate record based upon all three columns? create table item_meta_ref ( item_id varchar(50) not null, meta_id int not null, sort int not null, key (item_id, meta_id, sort) key (item_id) references item(id) on delete cascade ) type=InnoDB; Does that make sense? So I would like to put some type of constraint on the three columns. Thanks, Scott
