Unique Key in DBMS and Best Practices

If you are a beginner or intermediate, this article gives you a good overview of the unique key in DBMS and if you are an expert, it may help you get something new on the same or help refresh your knowledge. In this article post, you will learn completely unique key constraints to maintain the integrity and accuracy of the data.

Introduction to Unique Key

A unique key in DBMS comes into the picture when a DBMS manages a large amount of data. A UK is a key component and a popular part of any DBMS to ensure data integrity and accuracy in a table. A unique key acts like a unique identifier for each record in a database.

What is a Unique Key in DBMS?

The unique key constraint is a unique identifier which is the value of a column or set of columns that is unique for all rows in the table. It is used to enforce the integrity of the data stored in the database so that each row is uniquely identified and can be accessed and manipulated easily. The unique key does not allow duplicate values and creates a non-clustered index automatically. The unique key allows only one NULL value on the column where it is defined. One or more than one unique key can be defined on a table.

Types of Unique Keys

There are mainly two types of unique keys in DBMS – primary keys and secondary keys. Primary keys can be applied with only a column that is used to ensure every record is unique in a table. Secondary keys are other columns associated with the primary key to ensure that each record is unique. Secondary keys are often used to improve the performance of certain requirements.

Properties of Unique Key in DBMS

Refer to the below features of the unique key in DBMS –

1. Unique key can be defined on one or more than one column.
2. Unique keys can be created more than once in a single table.
3. Unique key creates a non-clustered index automatically.
4. Unique key allows only one null value in the column where it is defined.
5. Unique keys also can be taken as references using a foreign key.

How to Choose a Proper Unique Key?

The choice to choose the type of unique key depends on the specific requirements. We will take an example to explain it – If the database is being used to store all information about products then you can take the product id as the primary key. if the database is being used to store all information about customers then you can use the customer id as the primary key. on the other hand, if your database is being used to store the products as well as customers then you can use the customer id as the primary key and the product id as the secondary key.

It is important to ensure that the type of key is properly indexed to improve the performance of queries and data retrieval. You are suggested to choose a key that is both unique and stable, as changing the key can result in errors in data retrieval and analysis.

Importance of Unique Key in DBMS

A unique key constraint helps to maintain the data integrity and reliability in the relational database management system as follows –

1. The unique key in DBMS helps to prevent duplicate records and ensure that every record is unique in the table.
2. The unique key helps to ensure that the stored data is accurate, reliable, and unique.
3. The unique key helps to retrieve specific data from the database quickly and efficiently.
4. The unique key maintains the data integrity, eliminates the possibility of having inconsistent data, which can lead to the corruption of data, and ensures the stored data is accurate and consistent.

Best Practices to Implement Unique Key in DBMS

Refer to the below best practices to implement the unique key in DBMS –

1. Consider the type and purpose of data to store in the database while choosing a unique key. Let’s take an example – if you are storing personal information like names and addresses of customers then the primary key may be the best choice.
2. Making short, simple, and memorable keys are the best choice that is easy to understand and remember.
3. Avoid changing the unique key once it is already established on a column or a group of columns.

Applications of Unique Key in DBMS

You will learn here all about the application of unique key constraint in detail as follows –

1. Unique Key Creation While Table Creation

You will learn here to create a unique key while creating a new table.

A. Unique Key Creation on a Column Without Providing UK Name While Table Creation at Column Level

In this example, we will create a table named ‘UK_Test1’ and assign a unique key without providing a name with the ‘Vendor_Id’ column. In this scenario, SQL Server DBMS will provide a system-generated default name for this unique key which we will see ahead. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE NOT NULL UNIQUE,
ColumnN DATATYPE);

Example:

CREATE TABLE UK_Test1(
Vendor_Id INT NOT NULL UNIQUE,
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this UNIQUE key. In this scenario, SQL Server DBMS has provided a system-generated default name for this unique key (UQ__UK_Test1__D9CCC2A97815F6BF), which can be seen in the above screenshot marked with yellow color on the left side.

B. Unique Key Creation on a Column Without Providing UK Name While Table Creation at Table Level

In this example, we will follow the same above process but we will place a unique keyword at a different place in the query. Please refer to the below syntax –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE NOT NULL,
ColumnN DATATYPE,
UNIQUE (Column1));

Example:

CREATE TABLE UK_Test2(
Vendor_Id INT NOT NULL,
Vendor_Name VARCHAR(30) NOT NULL,
UNIQUE (Vendor_Id));

Screenshot: Below screenshot for reference –

In the above query example, we have not provided any specific name for this UNIQUE key. In this scenario, SQL Server DBMS has provided a system-generated default name for this unique key (UQ__UK_Test2__D9CCC2A968AF19A9), which can be seen in the above screenshot marked with yellow color on the left side.

C. Unique Key Creation on a Column With Providing UK Name While Table Creation at Column Level

In this example, we will create a table named ‘UK_Test3’ and assign a unique key named ‘uk_test3_vendor_id’ with the ‘Vendor_Id’ column. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE NOT NULL CONSTRAINT Constraint_Name UNIQUE,
ColumnN DATATYPE);

Example:

CREATE TABLE UK_Test3(
Vendor_Id INT NOT NULL CONSTRAINT uk_test3_vendor_id UNIQUE,
Vendor_Name VARCHAR(30) NOT NULL);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the UK name ‘uk_test3_vendor_id’ for this UNIQUE key, which can be seen in the above screenshot marked with yellow color on the left side.

D. Unique Key Creation on a Column With Providing UK Name While Table Creation at Table Level

In this example, we will follow the same above process but we will place a unique keyword at a different place in the query. Please refer to the below syntax –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE NOT NULL,
ColumnN DATATYPE,
CONSTRAINT Constraint_Name UNIQUE (Column1));

Example:

CREATE TABLE UK_Test4(
Vendor_Id INT NOT NULL,
Vendor_Name VARCHAR(30) NOT NULL,
CONSTRAINT UK_test4_vendor_id UNIQUE (Vendor_Id));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the UK name ‘UK_test4_vendor_id’ for this UNIQUE key, which can be seen in the above screenshot marked with yellow color on the left side.

E. Unique Key Creation on Multiple Columns With Providing UK Name While Table Creation at Table Level

In this example, we will create a table named ‘UK_Test5’ and assign a unique key named ‘UK_test5_vendor_id_Pan’ with the ‘Vendor_Id’ and ‘Vendor_PAN’ column. Refer to the below syntax –

Syntax:

CREATE TABLE Table_Name(
Column1 DATATYPE NOT NULL,
COLUMN2 DATATYPE NOT NULL,
ColumnN DATATYPE NOT NULL,
CONSTRAINT Constraint_Name UNIQUE(Column_Name1,Column_Name2,Column_NameN));

Example:

CREATE TABLE UK_Test5(
Vendor_Id INT NOT NULL,
Vendor_PAN INT NOT NULL,
Vendor_Name VARCHAR(30) NOT NULL,
CONSTRAINT UK_test5_vendor_id_Pan UNIQUE (Vendor_Id,Vendor_PAN));

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the UK name ‘UK_test5_vendor_id_Pan’ for this UNIQUE key, which can be seen in the above screenshot marked with yellow color on the left side.

2. Unique Key Creation After Table Creation

You will learn here to create a unique key after creating a new table.

A. Unique Key Creation on a Column Without Providing UK Name after Table Creation

In this example, We will create a unique key without providing a name with the ‘Vendor_Id’ column of an existing table named ‘UK_Test6’. In this scenario, SQL Server DBMS will provide a system-generated default name for this unique key which we will see ahead. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD UNIQUE (Column_Name);

Example:

ALTER TABLE UK_Test6
ADD UNIQUE (Vendor_Id);

Screenshot: Below screenshot for reference –

You can see in the above query example we have not provided any specific name for this UNIQUE key. In this scenario, SQL Server DBMS has provided a system-generated default name for this unique key (UQ__UK_Test6__D9CCC2A9CAAEC365), which can be seen in the above screenshot marked with yellow color on the left side.

B. Unique Key Creation on Multiple Columns With Providing UK Name after Table Creation

In this example, we will create a table named ‘UK_Test7’ and assign a unique key named ‘UK_test7_vendor_id_Pan’ with the ‘Vendor_Id’ and ‘Vendor_PAN’ column. Refer to the below syntax –

Syntax:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name UNIQUE (Column_Name1, Column_Name2);

Example:

ALTER TABLE UK_Test7
ADD CONSTRAINT UK_test7_vendor_id_Pan UNIQUE (Vendor_Id,Vendor_PAN);

Screenshot: Below screenshot for reference –

You can see in the above query example we have provided the UK name ‘UK_test7_vendor_id_Pan’ for this UNIQUE key, which can be seen in the above screenshot marked with yellow color on the left side.

3. Delete or Drop a Unique Key Constraint in DBMS

You will learn here to delete or drop an existing unique key.

Syntax:

ALTER TABLE Table_Name
DROP CONSTRAINT Constraint_Name;

Example:

ALTER TABLE UK_Test7
DROP CONSTRAINT UK_test7_vendor_id_Pan;

Screenshot: Below screenshot for reference –

As you can see that the primary key (UK_test7_vendor_id_Pan) is deleted permanently now and that can not be seen in the above screenshot marked in yellow color on the left side.

To Check the Behavior of a Unique Key Constraint in DBMS

You will learn here, how to check the behavior of an existing unique key using SQL queries. If you remember that we have already created a table named ‘UK_Test5’ where the columns are – Vendor_Id, Vendor_PAN, and Vendor_Name. Now, We will try to insert the two times the same value to check whether it is working or not. So Let’s start as follows –

Example 1: In the following example, we are executing an insert query in the ‘UK_Test4’ table –

INSERT INTO UK_Test5 (Vendor_Id, Vendor_PAN, Vendor_Name)
VALUES (12,1256,'New York, USA');

Screenshot: Below screenshot for reference –

As you can see that the above query is executed and the provided data is stored in the table.

Example 2: Now we will try to execute the same query with duplicate data to insert but the unique key should have thrown an error and aborted to store the duplicate data. So let’s try as follows –

INSERT INTO UK_Test5 (Vendor_Id, Vendor_PAN, Vendor_Name)
VALUES (12,1256,’New York, USA’);

Screenshot: Below screenshot for reference –

As you can see that the above query is executed but terminated by the unique key (UK_test5_vendor_id_Pan) associated with the combinations of columns ‘Vendor_Id’ and ‘Vendor_PAN’ in the table ‘UK_Test5’. Refer to the error message – ‘Violation of UNIQUE KEY constraint ‘UK_test5_vendor_id_Pan’. Cannot insert duplicate key in object ‘dbo.UK_Test5′. The duplicate key value is (12, 1256).’ So, this way we can check the behavior of the unique key constraint in SQL.

Difference between primary key and unique key

Refer to the below differences between primary key and unique key –

1. A table can have only one primary key but there can be more than one unique key in a table.
2. Primary key does not allow a null value but the unique key allows only one null value in the column.
3. Primary key creates a clustered index automatically but a unique key creates a non-clustered index.
4. A Primary key supports auto-incremental values but a unique key does not supports auto-incremental values.
5. Primary key does not allow to change or delete values stored in primary keys but the unique key allows to change or delete values stored in unique keys.

FAQs

Conclusion

This tutorial post described the basic to advanced level of knowledge of unique keys in DBMS. A unique key can be created by specifying the UNIQUE constraint on one or more columns in a table. This constraint ensures that no two rows in the table have the same values in the specified columns. In other words, each value in the unique key must be unique within the table.

We hope you would have liked this tutorial very well. Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DDL Full Form and How is it used in SQL?	DML Full Form and How is it used in SQL?
DCL Full Form and How is it used in SQL?	TCL Full Form and How is it used in SQL?
What is DQL in SQL?	What is Database Language?
What is Data Integrity in SQL?	What is Integrity Constraints in DBMS?
Key Constraints in DBMS With Fundamentals	Primary Key Constraint in SQL