UPDATE on duplicate columns when three are duplicate but not just one

Question

I'm implementing a MySQL database that uses an x/y/z primary key structure, where x and y are foreign keys and z is a local key.

My predicament is that I don't want a duplicate case to be activated unless all three are duplicated. Assuming I have this table structure.

   X    |    Y    |    Z    | Description
  34    |   F4    |   25    | This is a row
  34    |   F4    |   26    | This is also row
  C6    |   71    |   9A    | This is a row too

If I wanted to insert

  x=34,y=F5,z=25,description=This is a row as well

it would insert the new row because although x and z are duplicates, y is not. However, if I sent

  x=34,y=F4,z=25,description=This is the first row

it should update the entries description.

Can I do this with INSERT INTO ... ON DUPLICATE KEY, or there something more complex that needs to be done?

Answer 1

Of course you can. You just need to declare (x,y,z) as a primary or unique key:

 create unique index idx_table_x_y_z on table(x, y, z)

If the triplet already exists in the table, then you can use on duplicate key insert.