Help with INSERT IGNORE INTO

[djones]

I'm trying to insert unique data based on 2 fields. The product_id and the value_id combined should be unique. I'm not sure how to do this.

I have:

ID(primary key) -- not shown below

PRODUCT_ID(unique key)

VALUE_ID(unique key)

VARIATION_ID

 

I need product_id and value_id to be unique together. When I insert this data it only insert one row and I'm assuming it's because It's just looking at each unique fields by itself thus stopping at the next '37' insert. For example 37 and 11 should not insert twice, 37 and 3 ...etc

 

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '11', '4')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '3', '2')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '10', '4')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '3', '2')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '9', '4')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '5', '2')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '11', '4')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '5', '2')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '10', '4')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '5', '2')

INSERT INTO table (product_id, value_id, variation_id) VALUES ('37', '9', '4')

[Pikachu2000]

You can't set product_id and value_id as unique indices if you want to insert the same value into that field in multiple records.

[djones]

In Oracle (*ducks*) I can do this. I was hoping for a way in MySQL. So is this not possible? I can work around this, but this would save me several lines of PHP code.

[djones]

I forgot the IGNORE in the INSERTs above. They all include that...

[Pikachu2000]

If you use the IGNORE keyword' date=' errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.[/quote']

[PFMaBiSmAd]

Define your table with the following -

  UNIQUE INDEX `Index_name`(`product_id`, `value_id`)

[djones]

Wow, I'm made that real hard for myself. That's it. Thanks.

[Pikachu2000]

So an index spanning the 2 fields will allow that? I wish I'd known that about 3 months ago . . .