List:General Discussion« Previous MessageNext Message »
From:Tim Lucia Date:March 26 2006 12:09am
Subject:RE: Create a constraint over 3 columns.
View as plain text  
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:purcell5@stripped] 
Sent: Saturday, March 25, 2006 3:55 PM
To: mysql@stripped
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 

Thread
Create a constraint over 3 columns.Scott Purcell25 Mar
  • RE: Create a constraint over 3 columns.Tim Lucia26 Mar