SQL | UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct. This is useful when you want to guarantee that a column (or a combination of columns) contains unique values, which can help maintain data integrity.

Here are some key points and a few examples to illustrate the use of the UNIQUE constraint:

Single Column Unique Constraint:
Suppose you have a Users table, and you want to ensure that each email address is unique.
```
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE,
    FirstName VARCHAR(100),
    LastName VARCHAR(100)
);
```
Here, the UNIQUE constraint is placed on the Email column, ensuring that no two rows can have the same email address.
Multiple Columns Unique Constraint:
You can also use the UNIQUE constraint on multiple columns, which ensures that the combination of values across those columns is unique.
For instance, suppose you have a CourseRegistrations table where students register for courses. You want to ensure that a student doesn't register for the same course more than once:
```
CREATE TABLE CourseRegistrations (
    RegistrationID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    UNIQUE (StudentID, CourseID)
);
```
In this example, while individual StudentID and CourseID values can repeat in the table, the combination of a specific StudentID and CourseID can only appear once.
Adding and Dropping Unique Constraints:
You can also add or drop a UNIQUE constraint from an existing table.
- To add a unique constraint:
```
ALTER TABLE Users
ADD UNIQUE (Email);
```
- To drop a unique constraint:
```
ALTER TABLE Users
DROP CONSTRAINT unique_constraint_name;
```
  Note: The method to drop a unique constraint might differ slightly depending on your RDBMS. For instance, in some databases like MySQL, you might drop an index instead.
Considerations:
- NULL Values: Depending on the RDBMS, a column with a UNIQUE constraint can contain multiple NULL values. This is because NULL is considered distinct from any other value, including another NULL.
- Performance: The UNIQUE constraint typically creates a unique index on the column or set of columns to ensure uniqueness. This can offer performance benefits when querying based on these columns. However, it also means that the database has to check for uniqueness during every insert or update operation on these columns, which can have performance implications.

Always use constraints like UNIQUE judiciously, keeping both data integrity and performance considerations in mind.

How to use UNIQUE constraint in SQL:
- The UNIQUE constraint ensures that all values in a column are distinct.
```
CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Name VARCHAR(50) UNIQUE,
   Age INT
);
```

Creating a table with UNIQUE constraint:

Use the UNIQUE constraint during table creation.

CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Email VARCHAR(100) UNIQUE,
   Address VARCHAR(255)
);

Adding UNIQUE constraint to existing columns in SQL:
- Add a UNIQUE constraint to an existing column.
```
ALTER TABLE ExampleTable
ADD CONSTRAINT UC_Email UNIQUE (Email);
```

Multiple columns with UNIQUE constraint in SQL:

Use the UNIQUE constraint on multiple columns to enforce uniqueness across combinations.

CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   UNIQUE (FirstName, LastName)
);

Removing UNIQUE constraint in SQL:
- Remove the UNIQUE constraint from a column.
```
ALTER TABLE ExampleTable
DROP CONSTRAINT UC_Email;
```
Handling NULL values with UNIQUE constraint:
- UNIQUE constraints allow multiple NULL values, as NULL is not considered equal to NULL.
```
CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Code INT UNIQUE,
   Description VARCHAR(255)
);
```

Alternatives to UNIQUE constraint in SQL:

Use PRIMARY KEY or create a unique index as alternatives to enforce uniqueness.

CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Code INT,
   Description VARCHAR(255)
);

CREATE UNIQUE INDEX IX_Code ON ExampleTable (Code);

Checking existing UNIQUE constraints in a database:

Query system tables to check existing UNIQUE constraints.

SELECT
   TABLE_NAME,
   COLUMN_NAME
FROM
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
   CONSTRAINT_NAME LIKE 'UQ_%';

SQL UNIQUE vs. PRIMARY KEY constraints:
- The PRIMARY KEY constraint enforces both uniqueness and not allowing NULL values.
```
CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Name VARCHAR(50) UNIQUE,
   Age INT
);
```
Indexing and the impact on UNIQUE constraint:
- A UNIQUE constraint automatically creates a unique index on the specified column(s).
```
CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Code INT UNIQUE,
   Description VARCHAR(255)
);
```

UNIQUE constraint in combination with foreign keys:

Use UNIQUE constraints in combination with foreign keys to establish relationships.

CREATE TABLE Department (
   ID INT PRIMARY KEY,
   Name VARCHAR(50) UNIQUE
);

CREATE TABLE Employee (
   ID INT PRIMARY KEY,
   Name VARCHAR(100),
   DepartmentID INT,
   FOREIGN KEY (DepartmentID) REFERENCES Department(ID)
);

Ensuring data integrity with UNIQUE constraint:
- The UNIQUE constraint helps ensure data integrity by preventing duplicate values.
```
CREATE TABLE ExampleTable (
   ID INT PRIMARY KEY,
   Username VARCHAR(50) UNIQUE,
   Email VARCHAR(100) UNIQUE
);
```