intro
Let’s see what the SQL UNIQUE constraint is and how it works to ensure the wholeness of our data.
Constraints in SQL are a set of rules that are responsible for ensuring that the data in a database is whole. Among these set of rules, is the SQL UNIQUE constraint — notable for making sure that the values in a column(s) remain unique across a table in a database.
In this guide, you will find out what the UNIQUE constraint in SQL is, where you can use it, and how to use it in popular database technologies.
Let’s get right into it!
What Is the SQL UNIQUE Constraint?
When it comes to SQL, the UNIQUE constraint is there to ensure that the columns in a database are unique. It comes in handy when you are building a product where the uniqueness of your data matters.
At its core, it enforces that values in a group of columns are distinct from each other. Upon the application of the UNIQUE constraint on a set of columns, SQL will disallow any new or updated row from hosting a duplicate record in those columns.
It is important to note that
Time to explore the syntax of this useful clause!
UNIQUE Constraint in SQL: Operator Syntax
Use the following syntax to apply a UNIQUE constraint when creating a table:
1
CREATE TABLE demo_table (
2
column1 data_type UNIQUE,
3
column2 data_type
4
);
But, if you need your constraint to span across multiple columns, do this:
1
CREATE TABLE demo_table (
2
column1 data_type,
3
column2 data_type,
4
UNIQUE (column1, column2)
5
);
Instead, to add a UNIQUE constraint to an existing table, you can write:
1
ALTER TABLE customers
2
ADD CONSTRAINT unique_email UNIQUE (email);
Before you implement this constraint, verify that the data does not contain duplicates:
1
SELECT email, COUNT(*)
2
FROM customers
3
GROUP BY email
4
HAVING COUNT(*) > 1;
The above query retrieves all duplicate emails in the records of the customers table (if any).
SQL UNIQUE: Use Cases
Time to explore some use cases in real-world PostgreSQL examples. Keep in mind that you can easily extend them to other popular DBMS technologies, like MySQL, SQL Server, and Oracle
Note: The queries below will be executed in DbVisualizer, the world’s leading database client with the highest user satisfaction. In addition to being able to connect to several DBMSs, it offers great features and full support for all database PostgreSQL capabilities. Try DbVisualizer out now!
Example #1 — Applying UNIQUE on a Single Column
Consider a Customers table where we want to ensure that each customer has a unique phone number.
1
CREATE TABLE Customers (
2
id INT PRIMARY KEY,
3
name VARCHAR(50),
4
phone_number VARCHAR(15) UNIQUE
5
);
Now, in attempting to populate our database table with a duplicate value, PostgreSQL will fail with an error:
1
INSERT INTO Customers (id, name, phone_number)
2
VALUES
3
(1, 'Billy Jane', '674-2134')
4
(2, 'Joe Rogan', '674-2134');
Example #2: Composite UNIQUE Constraint on Multiple Columns
In this example, we will create a Bookings table with a composite UNIQUE constraint on the combination of columns: room_number and booking_date ensuring that each room can only have one booking per date:
1
CREATE TABLE Bookings (
2
booking_id INT PRIMARY KEY,
3
room_number INT,
4
booking_date DATE,
5
customer_id INT,
6
UNIQUE (room_number, booking_date)
7
);
Let’s attempt to insert bookings that conflict with this constraint:
1
INSERT INTO Bookings (booking_id, room_number, booking_date, customer_id)
2
VALUES
3
(1, 101, '2024-11-01', 1)
4
(2, 102, '2024-11-01', 2)
5
(3, 101, '2024-11-02', 3);
The UNIQUE constraint on (room_number, booking_date) ensures that the same room cannot be booked more than once on a specific date.
Best Practices and Considerations for Using SQL UNIQUE
Time to look at some of the best practices that should be taken into consideration when working with SQL UNIQUE:
It’s a wrap!
Conclusion
UNIQUE constraints in SQL are essential tools for maintaining data integrity in relational databases. They help ensure data consistency, improve query performance through automatic indexing, and provide a foundation for reliable data relationships.
Database developers should remember that while UNIQUE constraints add overhead to write operations, the benefits of guaranteed data consistency usually outweigh the performance costs and that, they should always test the impact of constraints in their specific use case.
Now that you have a grasp of this concept, put your newfound knowledge to the test. However, do note that SQL UNIQUE is not everything you need to know. While SQL is a powerful tool for querying and manipulating data, there are many other aspects of database management that are equally important, including data modeling, database design, and performance tuning, which can all be tackled effortlessly using SQL clients like DbVisualizer.
See you in the next blog, but until then, follow us on Dev.to to learn more about the newest trends in the database space!
FAQ
What is a UNIQUE constraint in SQL, and why do I need it?
A UNIQUE constraint is a rule you can set on a column(s) in a table to ensure that all values in that column are different from each other. For example, if you add a UNIQUE constraint to an email column in a users table, it will prevent two users from having the same email address. This helps keep your data accurate and prevents accidental duplicates in critical columns.
Can I have multiple UNIQUE constraints in the same table?
Yes, you can have multiple UNIQUE constraints in the same table. For example, you could set a UNIQUE constraint on both a username column and an email column. This way, each username and email in the table will be unique.
How do I add a UNIQUE constraint to multiple columns already containing data?
When adding a UNIQUE constraint to existing data, you need to first check for existing duplicates, clean up any duplicates, and then add the constraint using the ALTER TABLE command:
1
ALTER TABLE demo_table
2
ADD CONSTRAINT constraint_name UNIQUE (column_name);
How can I remove a UNIQUE constraint from a table if I no longer need it?
You can remove a UNIQUE constraint using the ALTER TABLE command with the DROP CONSTRAINT clause. Note that you must know the name of the UNIQUE constraint:
1
ALTER TABLE table_name
2
DROP CONSTRAINT constraint_name;
