SQL UNIQUE: Unique Constraint

Let's dive into the UNIQUE constraint in SQL.

The UNIQUE constraint ensures that all values in a column are different. In other words, it ensures that no duplicate values are entered in specific columns that are not a primary key.

There are two ways you can implement UNIQUE constraints in SQL:

At the column level: This applies the unique constraint directly on one column when creating or altering the table.
At the table level: This applies the unique constraint to the combination of more than one column when creating or altering the table.

Here's how you can use the UNIQUE constraint at the column level when creating a new table:

CREATE TABLE Customers (
    ID int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255),
    Country varchar(50),
    UNIQUE (ID)
);

In this example, the UNIQUE constraint is added to the ID column when the Customers table is created.

Here's how you can add a UNIQUE constraint to more than one column (at the table level) when creating a new table:

CREATE TABLE Customers (
    ID int NOT NULL,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255),
    Country varchar(50),
    CONSTRAINT UC_Customers UNIQUE (ID,CustomerName)
);

In this example, the UNIQUE constraint ensures that the combination of ID and CustomerName is unique across the whole table.

Here's how you can add a UNIQUE constraint to an existing table:

ALTER TABLE Customers
ADD UNIQUE (ID);

In this example, a UNIQUE constraint is added to the ID column in the Customers table.

Here's how you can drop a UNIQUE constraint:

ALTER TABLE Customers
DROP CONSTRAINT UC_Customers;

In this example, the UNIQUE constraint UC_Customers is removed.

Keep in mind that every PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. This is because primary keys must be unique and not null.

Also, remember that NULL values are considered distinct from all other values (including other NULLs), so if a column has a UNIQUE constraint and contains a NULL value, that doesn't prevent other NULL values from being inserted into the column.

Creating Unique Constraints in SQL:
- Description: Unique constraints ensure that all values in a column (or a set of columns) are distinct.
- Code Example:
```
ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);
```
Unique Key in SQL:
- Description: A unique key is a set of one or more columns that uniquely identifies each row in a table.
- Code Example:
```
CREATE TABLE your_table (
    column1 INT,
    column2 VARCHAR(50),
    UNIQUE (column1)
);
```

Adding a Unique Constraint to a Column in SQL:

Code Example:

ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column_name);

Removing Unique Constraints in SQL:

Code Example:

ALTER TABLE your_table
DROP CONSTRAINT unique_constraint_name;

Enforcing Uniqueness with SQL UNIQUE:
- Description: The UNIQUE keyword is used to enforce uniqueness on a column.
- Code Example:
```
CREATE TABLE your_table (
    column1 INT UNIQUE,
    column2 VARCHAR(50)
);
```
SQL UNIQUE vs PRIMARY KEY:
- Difference: Both enforce uniqueness, but a table can have multiple UNIQUE constraints, while it can have only one PRIMARY KEY.
- Example:
```
CREATE TABLE your_table (
    column1 INT PRIMARY KEY,
    column2 VARCHAR(50) UNIQUE
);
```

Unique Constraints in [Your Database System]:

Code Example (MySQL):

CREATE TABLE your_table (
    column1 INT,
    column2 VARCHAR(50),
    UNIQUE (column1)
);

Code Example (SQL Server):

ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column1);

Code Example (PostgreSQL):

ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column1);

Code Example (Oracle):

ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column1);

Handling Duplicates with SQL UNIQUE:

Description: When attempting to insert a duplicate value into a column with a UNIQUE constraint, an error is raised.

Example:

INSERT INTO your_table (column1, column2)
VALUES (1, 'Value1'); -- Valid

INSERT INTO your_table (column1, column2)
VALUES (1, 'Value2'); -- Error: Duplicate value in column1

Indexing and SQL UNIQUE Constraints:
- Description: Unique constraints automatically create unique indexes on the specified columns.
- Code Example:
```
CREATE TABLE your_table (
    column1 INT UNIQUE,
    column2 VARCHAR(50)
);
```

Unique Constraints on Multiple Columns in SQL:

Code Example:

CREATE TABLE your_table (
    column1 INT,
    column2 VARCHAR(50),
    UNIQUE (column1, column2)
);

Altering Tables to Add Unique Constraints in SQL:

Code Example:

ALTER TABLE your_table
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2);

Dropping Unique Constraints in SQL:

Code Example:

ALTER TABLE your_table
DROP CONSTRAINT unique_constraint_name;