SQL UNIQUE 🎯

Welcome to our comprehensive guide on the SQL UNIQUE keyword! In this tutorial, we'll dive deep into understanding the concept of UNIQUE, why it's essential, and how to use it effectively. Let's get started!

Understanding UNIQUE 📝

In SQL, the UNIQUE constraint ensures that a column or a set of columns in a table contain distinct and non-repetitive values. It's similar to the PRIMARY KEY constraint, but unlike a primary key, a table can have multiple columns with the UNIQUE constraint.

Why UNIQUE?

Ensures data integrity by preventing duplicate values in specified columns
Helps in creating efficient queries by reducing unnecessary data scanning
Simplifies error handling by providing a clear constraint violation message

Creating a UNIQUE Constraint 💡

To create a table with a unique constraint, you can use the CREATE TABLE statement with the UNIQUE keyword. Here's an example:

sql

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50) UNIQUE
);

In this example, we've created a Students table with StudentID as the primary key and LastName with the UNIQUE constraint.

Updating and Deleting UNIQUE Rows 💡

You can insert duplicate values into a column with a UNIQUE constraint, but SQL will throw an error. If you still want to insert the duplicate value, you can use the IGNORE or ON DUPLICATE KEY UPDATE statement, depending on your SQL dialect.

sql

INSERT INTO Students (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Students (FirstName, LastName) VALUES ('John', 'Doe'); -- This will throw an error

INSERT INTO Students (FirstName, LastName) VALUES ('John', 'Doe')
ON DUPLICATE KEY UPDATE LastName = 'Doe (Duplicate)';

In the above example, the second INSERT statement will throw an error. The third INSERT statement, however, will insert the duplicate value and update the LastName with a suffix to indicate the duplicate.

Quiz 🎯

Practical Application 💡

In real-world projects, you can use the UNIQUE constraint in various scenarios, such as maintaining unique usernames, email addresses, or product SKUs. It helps in maintaining data integrity and improves query performance.

Wrapping Up ✅

We've covered the basics of the SQL UNIQUE constraint and seen how it can help maintain data integrity in your database. In the next tutorial, we'll explore more advanced topics related to SQL. Until then, keep learning and happy coding!