ON DUPLICATE KEY UPDATE only for primary keys?

Question

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.

I am assuming that should have read `UNIQUE(username)`? – Uueerdo Jul 02 '18 at 23:18 — Uueerdo, Jul 02 '18 at 23:18
@Uueerdo yes sorry, I updated the question – Sneppy Jul 02 '18 at 23:32 — Sneppy, Jul 02 '18 at 23:32

score 3 · Accepted Answer · answered Jul 02 '18 at 23:12

3

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.

answered Jul 02 '18 at 23:12

Gordon Linoff

1,242,037
58
646
786

I didn't explain myself enough. I've updated the question, but your answer seems pretty clear _"triggers for both primary keys and unique keys"_ – Sneppy Jul 02 '18 at 23:33
@Sneppy . . . I'm going to emphasize that you should not be passing the `id` into an `insert`, because the database sets it automatically. – Gordon Linoff Jul 02 '18 at 23:37
actually I never said I'm providing the `id` value as well in the insert query – Sneppy Jul 02 '18 at 23:42
1

@Sneppy if you're not specifying the id, it should never generate the conflict. – Uueerdo Jul 02 '18 at 23:45
@Uueerdo yes, it generates the conflict on the `username` column and thus update that row. Anyway that table was just an example. I was looking for a far more general solution, that's way I wanted to differentiate between primary key and unique keys. – Sneppy Jul 03 '18 at 08:08
you can add the id as null or 0 and it will use the auto increment, so you can do that. if id > 0 it will use the update key, in case on id being 0 or null auto increment will be used. That said, with multiple unique keys, this can be a security issue, an user table for example. a sign up form with same email address as admin could overwrite the password or something like that. so, it's depending on situation... It is recommended to use this feature with only one KEY primary or unique Source https://mariadb.com/kb/en/auto_increment/#setting-explicit-values – blumanski Jun 28 '22 at 05:38

ON DUPLICATE KEY UPDATE only for primary keys?

1 Answers1