How to insert a row and ignore only UNIQUE contraint errors, without using SELECT

Question

I have a table with a column that must have UNIQUE values (but it could be also a multiple-column UNIQUE index, the problem is the same). In my PHP script I have to insert a row in that table.

I'm searching for a way, not MySQL-specific, to exit the PHP script if there's a problem, and the problem is not a violation of a UNIQUE constraint. To make things more easy I don't want to use a SELECT query before :D

Currently I'm doing something like this:

try
{
    $stmt = $dbh->prepare($someinsertquery);
    $stmt->bindParam(':somecol', $somecol);

    $stmt->execute();
}
catch (PDOException $e)
{
    $errcode = $e->getCode();

    if (! ($e->getCode() === '23000'))
    {
        echo 'Error inserting into db: ' . $e->getMessage();
        var_dump($e->getTrace());

        exit();
    }
}

The problem is that this way I miss also errors related to foreign keys. I think it's okay if I can't have foreign keys problems inserting a new row, but what if I'll change the table in future? I could use

PDOStatement::errorInfo[1]

but it is a driver specific error code. The same with ON DUPLICATE KEY UPDATE.

Your Common Sense · Answer 1 · 2013-05-22T17:07:51.327

2

$someinsertquery = "INSERT IGNORE INTO table SET somecol=?";
$stmt = $dbh->prepare($someinsertquery);
$stmt->execute(array(($somecol)));
if ($stmt->rowCount()) {
    //everything is okay
}

Please note the all this try..catch..echo..exit stuff is omitted intentionally, as it is totally useless here.

edited May 22 '13 at 17:07

answered May 22 '13 at 16:47

Your Common Sense

156,878
40
214
345

The problem is I want to be sure the error is caused by a violation of a UNIQUE constraint. With my code I can miss some other constraint violation. With your code _all_ the errors are ignored. – Marco Sulla May 22 '13 at 17:06
How's that? define "ignored"? which one for example? – Your Common Sense May 22 '13 at 17:08
Well, see this post for example: http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update . I can use "ON DUPLICATE KEY UPDATE", but as for errorInfo this is MySQL specific. – Marco Sulla May 22 '13 at 17:46
I don't understand what does on duplicate to do here. Do you see any *certain* problem with this code? – Your Common Sense May 22 '13 at 19:35
I must admit I misunderstood what INSERT IGNORE do exactly... I thought it ignored foreign key constraints as well. It works and it's a good solution. Unfortunately it's only for MySQL... I was searching for a generic solution, but I'm starting to think there's not such a solution. Maybe the best solution is to do a function that, given the database, gives you the error code the database uses to identify unique constraints violations. – Marco Sulla May 22 '13 at 21:19

score 0 · Answer 2 · answered May 22 '13 at 15:49

0

It's either that or do a select prior to insert to see if there will be a collision on any unique indexes.

answered May 22 '13 at 15:49

Orangepill

24,500
3
42
63

Yes, but I want to know if that error code means I'm violating some UNIQUE constraints _only_. If I do a SELECT I'll be sure, but I'd prefer to catch the exception. – Marco Sulla May 22 '13 at 16:07
you can be guaranteed that is at least the problem. It only responds with a single error so if there are other things wrong with the request you wouldn't know – Orangepill May 22 '13 at 16:18

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-05-22T23:27:55.687

0

There isn't really a database-agnostic way to trap SQL errors.

The SQL standards define SQLSTATE as a two-character class followed by a three-character subclass. Some two-character classes are defined by the standards; others are defined by the implementation.

The standards define the two-character class '23' as "integrity constraint violation". They define only one subclass for '23'. It's '001', which means "restrict violation". But implementations vary. Although MySQL apparently defines only '23000', PostgreSQL defines at least six different subclasses for '23'. (PostgreSQL subclasses distinguish foreign key violations from unique violations.)

So it appears that the only way to know what MySQL means by SQLSTATE '23000' is to look at the MySQL error code. Error 1022 means "duplicate key"; 1216 and 1217 have to do with foreign key constraints.

It might be useful to look at the source code for various database "connectors" to see how they're implemented. The php source code is online. (I'd expect that to include PDO.) So is ruby's ActiveRecord.

edited May 22 '13 at 23:27

answered May 22 '13 at 17:59

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Yes, but I exit the script if the error code is not equal to '23000'. I'm searching for a way, not MySQL-specific, to exit the script only if there's a problem and the problem is not a UNIQUE constraint is violated. – Marco Sulla May 22 '13 at 18:09
Apart the disk full, I don't really know what should my script do in such cases... and anyway this doesn't answer my question. – Marco Sulla May 22 '13 at 21:23
I've rewritten my answer. I seem to be having a brain-free day today. I'll shut up now. – Mike Sherrill 'Cat Recall' May 22 '13 at 23:28
Thank you, this is useful to know (there's very little about SQLSTATE on the web). I think I'll abandon the exception catch and adopt the @YourCommonSense solution, since on a second thought it allows me to not commit immediately the query. It's a pity that databases seem to not have a detailed and common error handling. – Marco Sulla May 23 '13 at 07:16

How to insert a row and ignore only UNIQUE contraint errors, without using SELECT

3 Answers3

Linked