UNIQUE Key in SQL

How can I make a column Unique in SQL?

We can make a column of a table unique in the following ways:

Method 1

We can make the column unique by adding the UNIQUE keyword at the end of its definition. Let's understand it with an example. We will create a table named employee, having the name column unique.

The above SQL Query creates a table named employee and makes the column name unique by adding the UNIQUE clause at the end of the column definition (name VARCHAR(100) UNIQUE). Thus, the new employee table we created has a column named name that contains unique values in its row.

Method 2

If the table already exists, the table gets modified, and the unique constraint is added to the column using the ALTER TABLE clause.

The above example adds the unique constraint to a column if the table employee already exists. This example uses the ALTER table clause to modify the table and make the column named name unique by using the UNIQUE constraint. The ALTER TABLE takes the name of the table (employee). It is then followed by the clause ADD CONSTRAINT and the name of the unique constraint we want to use. The unique constraint name we have used in the query is UQ_employee_name. The column name follows it in parenthesis used with the UNIQUE keyword.

Method 3

We can also apply a UNIQUE constraint on multiple columns simultaneously. Let's see how:

The above example uses the clause ALTER TABLE to modify the table employee. The constraint name (UQ_employee_name_address ) is used with the clause ADD CONSTRAINT. It gets followed by the UNIQUE keyword containing the column names as a list enclosed with parenthesis. The name and address columns in the above example make a unique key.

How do I change the Unique Key in SQL?

The unique key in SQL can be changed by first deleting the existing unique key. The new unique key gets created with a new definition.

Example

To change the unique key from UNIQUE KEY

user_id (user_id , account_id) to UNIQUE KEY user_id (user_id,account_id,pet_id).

The first way to change the unique key is to alter the existing table by dropping the previous unique key and adding a new one in the same statement.

Let's see it with an example.

It is done separately by dropping the existing key and creating the new one.

How to find a Unique Key in SQL?

We can find the UNIQUE key in SQL in the following two ways. Using the DISTINCT keyword or checking the constraint type with the WHERE clause.

Examples to find a unique key in SQL using the DISTINCT keyword

• select distinct * from employees; The above SQL statement retrieves any row if the table has at least a single unique column.

• select distinct first_name from employees; The above SQL statement removes all the duplicate values of first names and retrieves the unique first names from the table.

• select distinct count(*) from employees; The above SQL query retrieves the number of unique rows present in the table if it contains at least a single row having unique data.

Examples to find a unique key in SQL by checking the constraint type

Using the following query, we can also find all the UNIQUE constraints in a table.

We can create a table named ExampleTable.

The above SQL Query creates the table ExampleTable with fields named ID, FirstName, and LastName. The primary key gets set on the column ID. We use the constraint name UK_FName_LName with the CONSTRAINT clause. It gets followed by the UNIQUE keyword with the name of multiple columns as unique keys enclosed in parenthesis.

The above SQL Query matches the columns with the constraint type as UNIQUE and prints them with their constraint name in the output.

Output:

How to Remove a Unique Key in SQL?

Removing a UNIQUE key in SQL is similar to other constraints. The first step is to find the existing unique key in the table. The next step is to use the ALTER TABLE statement in SQL query with the DROP constraint that helps delete the unique key found in the previous step.