Stop next index from increasing upon integrity violation

Question

I've got the following method in my UserDAO class:

public function insert(Array $params)
{   
    $sql  = 'INSERT INTO users (firstname, lastname, email, password, register_date) ';
    $sql .= 'VALUES(:firstname, :lastname, :email, :password, FROM_UNIXTIME(:register_date))';

    $sth = $this->pdo->prepare($sql);
    $sth->bindValue(':firstname',     $params['firstname'],    \PDO::PARAM_STR);
    $sth->bindValue(':lastname',      $params['lastname'],     \PDO::PARAM_STR);
    $sth->bindValue(':email',         $params['email'],        \PDO::PARAM_STR);
    $sth->bindValue(':password',      $params['password'],     \PDO::PARAM_STR);
    $sth->bindValue(':register_date', $params['registerDate'], \PDO::PARAM_STR);

    return $sth->execute();
}

I've got a UNIQUE constraint on my email column, so whenever I'm inserting a new record with a duplicate email, it will throw an exception. That works fine, but I noticed that it still increments the next primary key id number while nothing was inserted, so it actually gets skipped over.

How can I stop it from increasing in a case like that, so that I still get a consecutive increase of the index number? (Like 1, 2, 3, 4 instead of 1, 3, 5, 7, etc).

If you have find solution accept the answer (with green button). — user2226755, Jul 16 '14 at 21:13

score 1 · Accepted Answer · answered Jul 16 '14 at 20:47

While it is not really an issue having a non-consecutive id column in a table, if you are concerned about the limit of the ids datatype you have a two realistic options.

Firstly simply SELECT check for the email's existence before the INSERT.

Or use something similar to this:

INSERT INTO users (firstname, lastname, email, password, register_date)
SELECT :firstname, :lastname, :email, :password, FROM_UNIXTIME(:register_date)
  FROM dual
 WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = :email)

You can use the returned row count to check whether a row has been INSERTed.

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Check if you've email in your database before send insert query.

//...
$sth = $this->pdo->prepare("SELECT count(*) FROM users WHERE email = ?");
$sth->execute(array($params['email']));
$rows = $sth->fetch(PDO::FETCH_NUM);

if ($rows[0]) {
    return $sth->execute();
}

How to get the number of rows grouped by column?

edited May 23 '17 at 11:50

Community

1
1

answered Jul 16 '14 at 20:46

user2226755

12,494
5
50
73

Isn't there any other way? Because I think this defeats the purpose of having a unique constraint. :/ – Kid Diamond Jul 16 '14 at 20:48
You really don't need the `COUNT(*)` just `1` will suffice. – Arth Jul 16 '14 at 20:48
@KidDiamond the unique constraint is there to protect your data, it is not really supposed to be used for information. Exceptions should only really be thrown because something exceptional (not expected) has happened. – Arth Jul 16 '14 at 20:51
@KidDiamond It is to inscrease speed. – user2226755 Jul 16 '14 at 20:51

Stop next index from increasing upon integrity violation

2 Answers2