Detect mysql update/insertion failure due to violated unique constraint

Question

This is kind of similar to this question:

PHP MySQL INSERT fails due to unique constraint

but I have a different twist. Let's say I have a table with only one column. The column's name is "title" and it has a unique constraint placed on it.

First I insert a row where title = "something". The next time I try to insert "something" it will fail due to a unique key constraint (which is good). What I'd like to do is allow it to fail, and check the error code provided by mysql to ensure it failed due to a unique key constraint. (i.e. let the database handle the uniqueness, and I just handle the error code and tell the user that title already exists when the result comes back).

Is there a way to do this?

Answer 1

Now that it's the year 2015, there are very few reasons not to be using PHP's PDO implementation.

The proper, modern, "OO" method for detecting and handling an insertion failure due to a key constraint violation is as follows:

try {
    //PDO query execution goes here.
}
catch (\PDOException $e) {
    if ($e->errorInfo[1] == 1062) {
        //The INSERT query failed due to a key constraint violation.
    }
}

The PDOException object has a lot more to say about the specific nature of the error, too (more detail than one could possibly ever want or need, seemingly).

Answer 2

http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

http://php.net/manual/en/function.mysql-errno.php

I've had to do this in the past, and it's not fun:

if( mysql_errno() == 1062) {
    // Duplicate key
} else {
    // ZOMGFAILURE
}

A note on programming style (Credits to jensgram from this answer)
You should always seek to avoid the use of magic numbers. Instead, you could assign the known error code (1062) to a constant (e.g. MYSQL_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

Answer 3

I believe the error code for duplicate keys is 1586. If you were to attempt to execute a query and then, on failure, check the error code using mysql_errno()/mysqli::errno() and compare it to 1586, that should do it. If it's not 1586, check what it actually is by echoing the error code after your query.

Answer 4

Why not just do a select first to see if the entry already exists. Or suppress an error altogether by using INSERT ON DUPLCATE KEY UPDATE, or even use the mysql IGNORE keyword. Why purposely cause an error?