A column or set of columns in a database system that uniquely identifies each tuple in the table is called a unique key. A unique key ensures that no two rows in the table have the same combination of values, so it enforces data integrity by preventing duplicity of the values in the table. In this article, we are going to see about the unique keys in DBMS along with their implementation in the table. Let’s start with the definition of a unique key.
What is a Unique Key?
Let’s consider one example of books in the library. In the library, each book has a unique identification number called ISBN(international standard book number ). Just like how each book in the library has its own ISBN which ensures that no two books are similar. In the same way in the database of customers, everyone has their unique email id so from that we can get to know about that customer in detail. So these examples illustrate the concept of unique identification in a database system. So ISBN for the book and email ID for a customer here work as a unique key.
Example:
|
Roll_no. |
Name |
Address |
Personal_id |
|---|---|---|---|
|
1 |
John |
Pune |
John@123 |
|
4 |
Merry |
Mumbai |
NULL |
|
18 |
Sheero |
Nagpur |
32467 |
|
20 |
Bisle |
Bengaluru |
B@127 |
In the above example, Roll_no can act as a primary since it is unique and there is no null value. Personal_id can act as a unique key because it is also unique and for one time we can keep its value as null so Personal_id is a unique key.
Advantages of Unique Key
- As we have discussed above, a unique key is used to remove duplicity in the table.
- A unique key helps to maintain the data integrity of the database by ensuring that no two records have the sane identifier, hence preventing inconsistencies and errors.
- It is also used as a primary key or foreign key in relational databases to establish relationships between tables, ensuring accuracy and consistency across related tables.
- It is automatically indexed in most DBMS implementations, which can improve query performance by providing faster searches and retrieval of data.
Difference Between Primary Key and Unique Key
|
Primary Key |
Unique Key |
|---|---|
|
A primary key cannot have a NULL value. |
Unique keys can have one NULL value. |
|
The table can have only one primary key. |
The table can have more than one unique key. |
|
Any change in primary key value may necessitate updates in related tables to maintain referential integrity. |
A unique key can be updated or deleted, it doesn’t affect the table’s integrity constraints. |
|
In some databases, the primary key can generate a clustered index by default. |
In some databases, a unique key can generate a non-clustered index by default. |
Syntax For Creating and Deleting Unique Key
For Creation
CREATE TABLE student(
student_id INT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
gender ENUM(‘Male’, ‘Female’, ‘Other’),
address VARCHAR(255),
);
In the above implementation we have created student table with student_id, name, email, gender, address as columns and email is unique key.
Let’s see another example where we will use two columns as unique key
CREATE TABLE employee(
employee_id INT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
employee_code VARCHAR(255) UNIQUE,
gender ENUM(‘Male’, ‘Female’, ‘Other’),
address VARCHAR(255),
);
In the above table employee, employee_code and email are two columns which are acting as unique key.
For Deletion
In the above table student, we can delete unique key email as follows
ALTER TABLE student,
DROP INDEX email;
Now email is not unique key in student table so duplicate values may be inserted in this column.
So like this we can declare and delete the unique key in any table.
Indexing and Performance
When you create unique key in database, it creates an index for columns involved automatically. So in a book index is present which helps you quickly find the information you wanted in book. Similarly in a database the index helps speed up searches for data, making it faster to retrieve information from the table. So by having unique keys you’re not only ensuring that your data is unique but also improving the performance of your database queries.
Frequently Asked Questions on Unique Key – FAQs
What is unique key in DBMS ?
A unique key is a constraint that ensures the values in a column or combination of columns are unique across all rows in table.
How does unique key doffer from a primary key ?
A unique key can have one NULL value but primay key cant have NULL value. Only one primary key is allowed in table but there can be more than one unique keys in a table.
Can a table have multiple unique keys?
Yes, a table can have multiple unique keys, each enforcing uniqueness within its own set of columns.
How to add unique key constraint to a column in an existing table ?
You can use the ALTER TABLE statement to add unique key constraint to a column. For example ALTER TABLR table_name, ADD CONSTRAINT contraint_name UNIQUE(column name) ; use this to add unique key.
Can a unique key constraint from a column removed after its been added ?
Yes, you can remove a unique key constraint from a column using the Alter TABLE statement, For example, ALTER TABLE table_name, DROP CONSTRAINT constraint_name; use this to delete unique key.