Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / MySQL

Ask Your Database for that Unique ID

5.00/5 (13 votes)
2 Sep 2015CPOL2 min read 35K  
Get your database to generate unique running IDs for your application

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:

SQL
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).

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

The code snippet in C# is given below:

C#
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:

SQL
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().

SQL
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
<?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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)