CREATE TABLE `test`.`A` ( `X` INTEGER, `Y` INTEGER, `Z` INTEGER, UNIQUE `unique_x_y_z`(x,y,z) ) ENGINE = MYISAM;
insert into a (x,y,z) values (1,2,3); insert into a (x,y,z) values (1,1,3); insert into a (x,y,z) values (1,2,3); 3 Duplicate entry '1-2-3' for key 1 #1062 -----Original Message----- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Saturday, March 25, 2006 3:55 PM To: mysql@lists.mysql.com Subject: Create a constraint over 3 columns. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
