Click here to Skip to main content
15,867,686 members
Articles / Database Development / MySQL
Tip/Trick

Ask Your Database for that Unique ID

Rate me:
Please Sign up or sign in to vote.
5.00/5 (13 votes)
2 Sep 2015CPOL2 min read 33.3K   9   7
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)


Written By
Instructor / Trainer
Singapore Singapore
“Live as if you were to die tomorrow. Learn as if you were to live forever.”
― Mahatma Gandhi

子曰:"三人行,必有我师焉;择其善者而从之,其不善者而改之."

Comments and Discussions

 
GeneralMy vote of 5 Pin
Maciej Los15-Jan-16 11:32
mveMaciej Los15-Jan-16 11:32 
QuestionWell Done Pin
Paul Conrad5-Sep-15 13:55
professionalPaul Conrad5-Sep-15 13:55 
AnswerRe: Well Done Pin
Peter Leow5-Sep-15 17:18
professionalPeter Leow5-Sep-15 17:18 
QuestionNice article Pin
De Wet de Jager2-Sep-15 23:21
professionalDe Wet de Jager2-Sep-15 23:21 
AnswerRe: Nice article Pin
Peter Leow3-Sep-15 3:25
professionalPeter Leow3-Sep-15 3:25 
QuestionGreat article Pin
Alexander Siniouguine2-Sep-15 19:32
Alexander Siniouguine2-Sep-15 19:32 
AnswerRe: Great article Pin
Peter Leow3-Sep-15 3:26
professionalPeter Leow3-Sep-15 3:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.