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
