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 · Accepted Answer · 2013-05-22 17:07:51Z

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, 2013 at 17:07

answered May 22, 2013 at 16:47

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marco Sulla Over a year ago

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.

Your Common Sense Over a year ago

How's that? define "ignored"? which one for example?

Marco Sulla Over a year ago

Well, see this post for example: stackoverflow.com/questions/548541/… . I can use "ON DUPLICATE KEY UPDATE", but as for errorInfo this is MySQL specific.

Your Common Sense Over a year ago

I don't understand what does on duplicate to do here. Do you see any certain problem with this code?

Marco Sulla Over a year ago

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.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-05-22 23:27:55Z

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, 2013 at 23:27

answered May 22, 2013 at 17:59

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

4 Comments

Marco Sulla Over a year ago

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 Over a year ago

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.

Mike Sherrill 'Cat Recall' Over a year ago

I've rewritten my answer. I seem to be having a brain-free day today. I'll shut up now.

Marco Sulla Over a year ago

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.

Orangepill · Accepted Answer · 2013-05-22 15:49:23Z

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, 2013 at 15:49

Orangepill

24.7k3 gold badges44 silver badges64 bronze badges

2 Comments

Marco Sulla Over a year ago

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.

Orangepill Over a year ago

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

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related