Create an Auto-Incrementing Id with Prefix (Varchar + Integer)

 

When creating a website, an application, or maybe software, you need to create a database to store the records. There are so many Database Management Systems(DBMS) applications, for example, Php MyAdmin, MySql Workbench. Whatever application you use, you must insert the records taken from the application, with a unique record id.

Yes, it is a simple thing which is creating a unique record id for every record. In Php MyAdmin, we can create a field with name as Id, index as primary, type as Int and, by ticking on auto-increment we can create a record Id field with the integer data type. Also, it is an auto-increment field(No need to add the values to that field, when inserting the records. Values are automatically added by auto-incrementing one by one). It gives unique numbers as IDs to every record in the table. But it is a little bit not suitable when you are working on a project or when developing an application or software.

Therefore we can create an Id with other characters that related to a specific table in the database. It is very easy to identify the table by looking out the record Id, and also it gives a rather pleasant look to your work. For example, we can create a Users table with an Id field which values in the format of USR_001, USR_002, ..., USR_023, etc, also an Admins table with an Id field which values in the form of ADM_001, ADM_097 likewise.

Let's see how we can do this, and I'm going to tell you to step by step as before. Here I use Php MyAdmin to tell you how to do this task but I use the SQL commands, not the interface of the Php MyAdmin. Hence you can use those codes in your preferred database management system application or in command prompt also.

• Step 01: As the first step, here I create a table in the database as the users, And I create a user_id field to insert values in the form of USR_###. I use the data type of user_id field as varchar because we use characters in the values.

CREATE TABLE users(user_id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', first_name VARCHAR(30), last_name VARCHAR(30));

Now I have created a table with no records.

• Step 02: Create another table to create a trigger to format the values in the user_id field of the user table. This table I named as users_seq. Here I use the data type of id field as an integer. Because we use this table to make our user_id field in the users table with characters.

CREATE TABLE users_seq(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

• Step 03: Now create a trigger on the users table to connect both tables and to do our task. We need a trigger to insert the values first to the users_seq table and then insert the values to the users table to the field of user_id in the form of USR_###. 

DELIMITER $$

CREATE TRIGGER tg_users_insert

BEFORE INSERT ON users

FOR EACH ROW

BEGIN

  INSERT INTO users_seq VALUES (NULL);

  SET NEW.user_id = CONCAT('USR_', LPAD(LAST_INSERT_ID(), 3, '0'));

END$$

DELIMITER ;

In Php MyAdmin, you can see the created triggers of a table in the trigger tab as shown below. Go to the table, and then go to the trigger tab.

• Step 04: Now simply insert few records to users table and see the values of the user_id field.

INSERT INTO users(first_name, last_name) VALUES ('Jhon', 'Doe');

INSERT INTO users(first_name, last_name) VALUES ('Kevin', 'Mogan');

Here you go... All the values of the user_id field are in the form of USR_###.

Likewise, you can create a field for the Id field, or maybe another field which is values contains the characters also. You can create your own form of the values. You only need to do is, changing the trigger values as you wish. Use these tips and make your project or code pleasant with EAZY PROGRAM. If you have any issues or if this post was helpful for you, please leave a comment. Also, share this with your friends on social media and follow us to know these kinds of useful tips. See you soon. Stay safe...!

FOLLOW US: