Member Avatar for MDanz

i have a column in mysql called origin which is type integer(not ascending or descending). All rows have an origin assigned to them can't be duplicated... e.g. two rows with the same origin(integer).


when inserting a new row. Just say row 1 has origin 15 and i'm inserting a new row with the same origin(15) how do i go to the next available integer to prevent a duplicate origin?

Edited by MDanz because: n/a
  • 2 Contributors
  • 3 Replies
  • 105 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by JRM

Dani AI

Generated

A few practical points to make this robust and safe (and to expand on 's "check then insert" idea for ):

Make the column enforce uniqueness at the database level. Add a UNIQUE index on origin so accidental races cannot create duplicates. If origin is just a sequential id, prefer a true AUTO_INCREMENT primary key instead of managing numbers yourself.

If you must pick the "next available" integer, avoid a plain SELECT-then-INSERT without protection: that pattern has a race condition (two processes read the same free value and both try to insert). Two safer patterns:

  1. Optimistic: let the UNIQUE constraint enforce uniqueness and retry on duplicate-key error. This serializes only the failing attempt and scales well.

Example (PDO, simplified):

$origin = 15;
$maxTries = 100;
$stmt = $pdo->prepare("INSERT INTO my_table (origin, other) VALUES (:origin, :other)");

for ($i = 0; $i < $maxTries; $i++) {
  try {
    $stmt->execute([':origin' => $origin, ':other' => $value]);
    break; // inserted
  } catch (PDOException $e) {
    if (isset($e->errorInfo[1]) && $e->errorInfo[1] == 1062) {
      $origin++; // or compute a smarter next candidate and retry
      continue;
    }
    throw $e;
  }
}
  1. Deterministic: compute a free value in SQL (smallest gap) and then insert, but still handle duplicate-key on insert. To find the smallest missing positive integer in MySQL:
SELECT CASE
  WHEN NOT EXISTS (SELECT 1 FROM my_table WHERE origin = 1) THEN 1
  ELSE (SELECT MIN(t1.origin + 1)
        FROM my_table t1
        LEFT JOIN my_table t2 ON t2.origin = t1.origin + 1
        WHERE t2.origin IS NULL)
END AS next_origin;

Notes and cautions:

  • Always keep the UNIQUE index. It is the last line of defense.
  • Limit retries and add logging to avoid infinite loops.
  • If strict sequence semantics are required (no gaps, strong ordering), use a dedicated sequence table with SELECT ... FOR UPDATE or use GET_LOCK to serialize access — at the cost of throughput.
  • For most use cases, AUTO_INCREMENT or letting the DB assign the id is simpler and safer.

Recommended Answers

All 3 Replies

Member Avatar for JRM

before you insert, do a query for the origin number that you are about to insert. If the query comes up empty, you can insert the number.

Member Avatar for MDanz

before you insert, do a query for the origin number that you are about to insert. If the query comes up empty, you can insert the number.

i've done the part if it comes up empty..

if it is doesn't come up empty how do i insert an available origin number(not used by any in the table)?

Member Avatar for JRM

Maybe i'm not understanding the issue?
I don't know where the "available origins" come from.
My thought was, that you HAD one and wanted to make sure it wasn't already there.
You do a "select" where origin = $myNewTestOrigin. If it's empty, then your test origin can be put into a second "insert" query and insert it..
It's two queries.
Am I missing the point? ( no pun intended)

Edited by JRM because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.