For an instructor lead, in-depth look at learning SQL click below.
SQL, or Structured Query Language, is a powerful tool used for communicating with and manipulating databases. It’s an essential skill for many roles in the tech industry. In SQL, databases, tables, and records all have unique identifiers. In this blog, we will dive into identifying the different ways to generate unique identifiers in SQL.
1. AUTO_INCREMENT
In SQL, AUTO_INCREMENT keyword allow you to create a unique number to be generated whenever a new record is inserted into the table. This can be helpful when you want to create a unique id for every record in a table.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Employees ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); |
In the example above, for the Employees table, ID column will be a unique identifier and its value will be incremented for each new record.
2. UUID()
Another way to generate unique identifiers using SQL is the UUID(). The UUID() function is used to generate a Universally Unique Identifier (UUID) for each record which can also act as a primary key.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees ( ID UUID PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int ); |
In the example above, for Employees table, ID column will be a unique identifier and its value will be generated via UUID() for each new record.
3. NEWID()
For SQL Server, the NEWID() function generates a uniqueidentifier value using the algorithm for generating globally unique identifiers. This is a 16-byte GUID value.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Employees ( ID uniqueidentifier NOT NULL DEFAULT newid(), LastName nvarchar(50), FirstName nvarchar(50), Age int, PRIMARY KEY (ID) ); |
In the above SQL Server example, the ID column will be a unique identifier and its value will be generated by the NEWID() function for each new record.
Conclusion
Generating unique identifiers in SQL is an important aspect while designing database and managing data records. Depending on the database and requirement, you might want to use AUTO_INCREMENT, UUID() or NEWID() functions in SQL for creating unique identifiers.