[SOLVED] ON DUPLICATE KEY UPDATE only for primary keys?

ON DUPLICATE KEY UPDATE only for primary keys?

Is there a way to restrict "ON DUPLICAYE KEY UPDATE" to only trigger if the duplicate key is the PRIMARY KEY of the table? (and not if the conflict is generated by a UNIQUE KEY)

For example in the following table:

CREATE TABLE users (
    id INT(16) UNSIGNED AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id),
    UNIQUE (username)
);

I would like to trigger the update only if the id column generate the conflict, and throw an error as usual in case the conflict happened because of the unique key username.

Edit:

I'm working on a very simple PHP framework. Previously I had a single method save() that discriminated between INSERT and UPDATE based on the presence of the id property on the object on which it was called.

Now I rewrote this method using the INSERT INTO ... ON DUPLICATE KEY UPDATE query, but when I try to insert (for example) a user with an already existing username value, it updates that user instead of throwing an error.

I know this is the correct behaviour, I just wanted to know if there's a way to achieve the same result only on the PRIMARY KEY.

Solution

on duplicate key triggers for both primary keys and unique keys.

In your case, the primary key is an auto-incremented column, so you should not be inserting a value. Period.

Hence, you can get the behavior you want by simply not including the on duplicate key clause and leaving out the id from the insert.