Unique Key in DBMS: Definition, Example, and Difference From Primary Key

How Does a Unique Key Work?

Before a row goes in, the database checks the value against what is already stored. It does this on every INSERT and every UPDATE.

Found a match? The statement is rejected and you get an error. The table stays as it was.

To make this fast, the database builds a unique index on the column. Most systems use a B-tree for this. The index is sorted, so finding a value takes very little time, even with millions of rows.

Let's see it on a small users table:

CREATE TABLE users (
 id INT PRIMARY KEY,
 email VARCHAR(100) UNIQUE
);

INSERT INTO users VALUES (1, 'amit@example.com');
INSERT INTO users VALUES (2, 'amit@example.com');

The first insert works. The second one fails. The id is new, but the email is already taken.

MySQL shows a "Duplicate entry" error. PostgreSQL calls it a unique constraint violation. A composite unique key follows the same logic. The difference is that the database compares the whole combination.

Say the unique key covers student_id and course_id. The pair (101, 5) can only appear once. But student 101 can still join other courses. And course 5 can still have other students.

A few details catch people off guard.

• NULL values. MySQL, PostgreSQL, and Oracle allow several NULLs in a unique column. NULL is not equal to another NULL. SQL Server is stricter. By default, it allows only one NULL. If you may switch databases later, test this early.
• Letter case. Are Amit@Example.com and amit@example.com duplicates? It depends on the collation. MySQL and SQL Server are case-insensitive by default, so they count as the same value. PostgreSQL treats them as different unless you handle it yourself.
• Two users at once. Two transactions may try to insert the same value at the same time. The database makes one wait. The first to commit wins. The other gets an error.
• Deferred checks. PostgreSQL and Oracle can delay the check until the end of a transaction. This helps when you swap values between two rows. The values clash for a moment in the middle, and a deferred check allows that.

You could check for duplicates in your app code instead. But that is risky. A bulk import, another script, or a manual edit can skip your code.

Also read: Primary Key vs Unique Key

Types of Unique Keys in DBMS

A unique key can guard one column or several. That gives you two types: single-column and composite. The rule works the same way in both. Only the thing the database compares is different.

1. Single-Column Unique Key

Put a unique key on one column, and no value in it can repeat. Email is the classic case. Usernames and passport numbers work too.

CREATE TABLE members (
    member_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    mobile VARCHAR(15) UNIQUE
);

Here, email and mobile each have their own unique key. Two members can't share an email. They can't share a mobile number either. The database checks each column separately.

Replace the closing lines of the single-column part:

Both email and mobile carry their own unique key. A member can't register with an email that's already taken, and a mobile number can't be reused either. Each column is checked separately.

2. Composite Unique Key

Sometimes one column can't tell you whether a row is a duplicate. Take a meeting room booking system. Room 5 will be booked many times, and slot 1 will be booked many times too. What you can't allow is Room 5 in slot 1 being booked twice.

A composite unique key handles this. It looks at the pair, not at each column alone.

CREATE TABLE room_bookings (
    booking_id INT PRIMARY KEY,
    room_id INT,
    slot_id INT,
    UNIQUE (room_id, slot_id)
);

Row 4 is rejected because room 5 in slot 1 is already taken by row 1. Rows 2 and 3 are fine, since each repeats only one value.

Two things to remember with composite keys:

• Column order matters for the index. (room_id, slot_id) speeds up searches on room_id, but helps less for slot_id alone.
• If a column in the pair is NULL, the result depends on the database. Test it before relying on it.

Also read: Super Key in DBMS

Unique Key in DBMS With Example

Let's say a small company keeps its people in a staff table. The ID is the primary key. Email and mobile number must never repeat, so both get a unique key.

CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    full_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    mobile VARCHAR(15) UNIQUE
);

Now add three people.

INSERT INTO staff VALUES (1, 'Riya Sharma', 'riya@company.com', '9876543210');
INSERT INTO staff VALUES (2, 'Karan Mehta', 'karan@company.com', '9123456789');
INSERT INTO staff VALUES (3, 'Neha Verma', 'neha@company.com', NULL);

All three go through. The emails are different, and so are the mobile numbers. Neha has no mobile number yet, and most databases accept that.

Now try to break the rule with a new hire who reuses Riya's email.

INSERT INTO staff VALUES (4, 'Amit Singh', 'riya@company.com', '9000000001');

This fails. The ID and the mobile number are new, but the email already belongs to staff 1. One duplicate is enough to reject the whole row.

Updates are checked the same way. Here we try to give Neha Karan's email.

UPDATE staff
SET email = 'karan@company.com'
WHERE staff_id = 3;

The database blocks this too, because the change would create a duplicate.

Good data is where AI starts. The Universal AI Program by MIT Open Learning teaches you Python, machine learning, and large language models at your own pace.

How to Create a Unique Key in DBMS

You can create a unique key in two ways. You can define it when you create the table. Or you can add it later to a table that already exists.

You can also remove it when you no longer need it.

The basic syntax is similar across most databases. The small differences appear mainly when you drop a constraint. We cover those below.

1. Creating a Unique Key During Table Creation

The easiest time to add a unique key is when you build the table. There are two ways to write it.

• Column-level syntax. You place the UNIQUE keyword right next to the column.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

This is short and easy to read. It works well for single-column rules.

The database gives the constraint a default name. That name is often hard to read, so you may want to set your own.

• Table-level syntax. You write the constraint after all the columns. You can also give it a name.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email)
);

A named constraint is easier to manage. When an error appears, the name tells you which rule failed. It also makes the drop command simple later.

Table-level syntax is the only choice for composite keys.

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    CONSTRAINT uq_student_course UNIQUE (student_id, course_id)
);

A good naming habit is uq_tablename_columnname. It keeps things clear as your schema grows.

2. Adding a Unique Constraint to an Existing Table

Sometimes the table already exists. You realize a column should have been unique all along. Use ALTER TABLE.

ALTER TABLE users
ADD CONSTRAINT uq_users_phone UNIQUE (phone_number);
For a composite key, list the columns inside the brackets.
ALTER TABLE enrollments
ADD CONSTRAINT uq_student_course UNIQUE (student_id, course_id);

There is one catch. The table must not already contain duplicates in those columns. If it does, the command fails.

Check for duplicates first. This query finds them.

SELECT phone_number, COUNT(*)
FROM users
GROUP BY phone_number
HAVING COUNT(*) > 1;

If it returns rows, you have duplicates. Fix or remove them first. Then run the ALTER TABLE command again.

On large tables, adding a unique constraint can take time. The database has to build an index over all existing rows. Some systems may lock the table while it does this. Run it during a quiet period if you can.

3. Removing a Unique Key

You may need to drop a unique key if the business rule changes. The command depends on your database.

• MySQL treats the unique key as an index.

ALTER TABLE users
DROP INDEX uq_users_phone;

• PostgreSQL, Oracle, and SQL Server treat it as a constraint.

ALTER TABLE users
DROP CONSTRAINT uq_users_phone;

This is why naming your constraints matters. If you used a default name, you first have to look it up. In MySQL, you can run SHOW INDEX FROM users;. In PostgreSQL, you can use \d users in the psql tool.

Dropping a unique key also removes its index. Searches on that column may become slower afterward. Also, check whether another table depends on it. A foreign key can point to a unique column. If it does, you must drop the foreign key first.

Also read: Primary Key in SQL Database: What is, Advantages & How to Choose

Unique Key Constraints in DBMS

A unique key is one of several constraints in a database. Constraints are rules that control what data a table can hold. The database enforces them automatically.

A unique constraint has one job. It stops duplicate values in a column or a group of columns.

The constraint applies at the database level. It does not matter whether the change comes from an app, a script, or a person typing a query. The rule holds every time.

Here is how a unique constraint behaves in practice:

• Checks values on every INSERT and UPDATE.
• Cover one column or several columns together.
• Have a name, which makes errors easier to read.
• Usually creates an index in the background.
• Allows NULL in most databases.
• A table can have many of them.

How It Compares With Other Constraints

A unique constraint works alongside other constraints. Each one solves a different problem.

A few of these are worth a closer look.

• UNIQUE and NOT NULL together. Many designers combine these two. The column then behaves almost like a primary key. Every row must have a value, and no value can repeat.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

This is a good pattern for fields like email or username. You do not want blank values, and you do not want duplicates.

• UNIQUE and FOREIGN KEY. A foreign key normally points to a primary key. But it can also point to a unique key. This is allowed in most databases.

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_code VARCHAR(10) UNIQUE
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_code VARCHAR(10),
    FOREIGN KEY (dept_code) REFERENCES departments(dept_code)
);

Here, employees.dept_code refers to a unique column, not the primary key. This works because dept_code is guaranteed to be distinct. To learn more about how tables link together, read our guide on foreign key in DBMS.

• UNIQUE and CHECK. These two often work as a pair. UNIQUE stops repeats. CHECK limits the allowed range or format.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    sku VARCHAR(20) UNIQUE,
    price DECIMAL(10,2) CHECK (price > 0)
);

Each sku must be different. Each price must be above zero.

When to Use a Unique Constraint

Use one when a value must be one of a kind but is not the main row ID. Good examples include:

• Email addresses
• Usernames
• Phone numbers
• National ID or passport numbers
• Product SKUs
• Employee codes

Skip it when repeats are normal. A city name, a job title, or an order date will repeat many times. A unique constraint on them would cause problems.

Also think before you add one to a very large table. The new index needs storage. It also adds a small cost to every insert and update. For the main row identifier, use a primary key in DBMS instead. It is built for that purpose.

Also read: Candidate key DBMS

Primary Key vs Unique Key in DBMS

Primary key and unique key look alike. Both stop duplicate values. Both can create an index. But they serve different purposes.

A primary key is the official identity of a row. A unique key protects a column that must not repeat.

Think of a school. Each student has a roll number. That is the primary key. Each student may also have a unique email address. That is a unique key. The roll number is how the school tracks the student. The email is another value that cannot be shared.

Difference Between Primary Key and Unique Key in DBMS

Also read: What Are Attributes in DBMS ? 10 Types and Their Practical Role in Database Design

Advantages and Limitations of a Unique Key

A unique key is a simple rule that keeps data clean without extra code. It has clear benefits and a few limits.

Advantages of Unique Key 

• Maintains data integrity. The database checks every insert and update. Bad data is stopped before it is saved. This holds no matter which app or script writes to the table.
• Prevents duplicate entries. Two accounts with one email or two products with one SKU cause real problems. A unique key blocks them at the source. Cleaning up duplicates later is slow and risky.
• Helps enforce business rules. Many rules are really unique, like one booking per room per time slot. A composite unique key turns them into database logic.
• Speeds up lookups. The index behind a unique key makes searches on that column fast.

Limitations of Unique Key

• NULL behavior can vary across DBMSs. MySQL, PostgreSQL, and Oracle allow many NULLs in a unique column. SQL Server allows only one by default. A design that works in one system may fail in another. Adding NOT NULL gives you strict control.
• Additional constraints can affect insert and update operations. Every unique key needs an index. Each write must update it and run the duplicate check. For one or two keys, the cost is tiny. On write-heavy tables with many keys, it adds up.
• Not intended to replace a primary key in every situation. A unique key usually allows NULL, so it cannot always identify a row. Most ORMs and foreign key designs also expect a primary key.

Also read: DBMS Tutorial For Beginners: Everything You Need To Know

Conclusion

A unique key keeps a column, or a group of columns, free of duplicates. The database checks it on every insert and update, so the rule holds no matter which app or script changes the data.

It differs from a primary key in three ways. A table can have many unique keys but only one primary key. A unique key usually allows NULL. And it protects a value rather than naming the row.

Use a unique key for fields like email, mobile number, or a product code. Check how your database treats NULL before you rely on it. And keep the primary key as the main identifier of every table.

Have any questions about Data Science courses? Book a free consultation call with our experts and get personalized guidance on the right learning path for you.