Resolving DUPLICATE for a column with UNIQUE

Question

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test title-2.

Is there a way to make a unique value upon DUPLICATE?

INSERT INTO table1
  (title, abr, name) 
  VALUES
  ('title', 'abr', 'name')

In this typical example, all three columns are UNIQUE. When the INSERT fails, I do not know which col caused the error to change its corresponding value.

How can I make a query to change value to value-identifier upon duplicate error?

Answer 1

I would agree with @BillThor that what you want to do may create some problems but what you need is ON DUPLICATE KEY UPDATE statement so your query would be something like this:

INSERT INTO table1
(`title`, `abr`, `name`) 
VALUES
('title', 'abr', 'name')
ON DUPLICATE KEY UPDATE `title` = CONCAT(`title`, '-2');

You can change CONCAT to any other function or your own stored procedure.

Answer 2

I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title.

If I had to resolve your problem, I would build a query for each unique key of the form:

SELECT  title
FROM    table1
WHERE   title STARTS WITH ?

The results will need to be parsed to see what the suffix should be. It might be possible to do this in a stored procedure. However, I would strongly argue that the data model needed to be fixed, not the code.