Ask Your Database for that Unique ID

The Assumption

One of the many requirements for an online reservation system states that while a customer is making a reservation via a web form, a unique ID, acting as the reservation number, is to be displayed on this form. It also specifies that each ID shall consist of a fixed prefix followed by a running integer, e.g., ABC_1, ABC_2, ABC_3, and so on. These IDs will be used to identify individual reservations and be stored in the database upon submission. You are tasked to find a way to generate such IDs.

The Thinking

The key to upholding the uniqueness of each ID lies with the running integer. In a multi concurrent user environment, how to ensure that each new reservation activity gets an integer that is one larger than that of its immediate precursor? There is a handy way - that is, making use of the auto-increment primary key of a database table! In a nutshell:

• Create a simple database table, say "uidTable" that has an auto-increment primary key field called id;

• When a user navigates to the reservation form for the first time, fire a SQL INSERT to the "uidTable" followed by retrieving the newly generated ID; and

• Concatenate this ID with the prefix and you get the unique ID for that reservation form.

Let's see how this can be accomplished in .NET-SQL Server and PHP-MySQL environments respectively.

The Doing

.NET-SQL Server

Create a table in SQL Server as shown below:

CREATE TABLE uidTable
(
     id int IDENTITY PRIMARY KEY,
     dummy int
);

These are the SQL statements that insert a value, say 1, into the table and immediately retrieve the unique id generated for the insertion. Find out more on SCOPE_IDENTITY (Transact-SQL).

INSERT INTO uidTable (dummy) VALUES (1);
SELECT SCOPE_IDENTITY()

The code snippet in C# is given below:

using (SqlConnection con = new SqlConnection(ConnectionString)) {
    
    int newID;

    string sql = "INSERT INTO uidTable (dummy) VALUES (1); SELECT CAST(SCOPE_IDENTITY() AS int)";

    using (SqlCommand cmd = new SqlCommand(sql, con)) {

        con.Open();

        newID = (int)cmd.ExecuteScalar();
    }
}

// Concatenate newID with the prefix

PHP-MySQL

Create a table in MySQL as shown below:

CREATE TABLE uidTable (
    id int AUTO_INCREMENT PRIMARY KEY
);

These are the SQL statements that insert a null value into the table and immediately retrieve the unique id generated for the insertion. Find out more on LAST_INSERT_ID().

INSERT INTO uidTable VALUES (null);
SELECT LAST_INSERT_ID();

Besides LAST_INSERT_ID(), you can use the mysqli_insert_id() function in PHP to return the unique id.

The code snippet in PHP is given below:

<?php

$con=mysqli_connect("IP_address","username","password","database_name");

// Check database connection
if (mysqli_connect_errno())
  {
  echo "Unable to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_query($con,"INSERT INTO uidTable VALUES (null)");

$newID = mysqli_insert_id($con); 

mysqli_close($con);

// Concatenate $newID with the prefix

?>

Keep it Empty

The sole purpose of the "uidTable" is to generate an auto-increment id for each insertion. The inserted rows in this table are of no use and can be deleted if storage space is of concern.