UNIQUE KEY in SQL

Last Updated: 23rd June, 2024

Unique could be a constraint in SQL that guarantees the values stored in a column or a group of columns are distinct from each other. It is frequently utilized to anticipate duplicate entries from being entered into a table. Interesting constraints can moreover be utilized to guarantee that no null values are stored in a column. When a Unique constraint is specified, SQL will automatically check each row within the table to guarantee that the esteem put away within the column or group of columns is one of a kind. On the off chance that a duplicate value is found, an mistake will be tossed.

SQL UNIQUE KEY Constraint on CREATE TABLE

A retail company was facing a unique challenge. They needed to extend their client dependability program but required a way to guarantee that clients couldn't sign up for the program different times and take advantage of the rewards. To do this, they chosen to utilize a Unique constraint in their SQL database. The Unique constraint guaranteed that no client might sign up for the dependability program more than once. In case a client endeavored to sign up once more, the database would return an error message and not permit the client to enlist. This permitted the company to secure their dependability program from extortion, whereas still giving their clients with the rewards they merited. Lets help them with it further.

Defining a Unique Key Constraint on Single Column

Syntax

CREATE TABLE table_name 
(
column1 datatype constraint,
column2 datatype constraint,
...,
columnN datatype constraint,
CONSTRAINT key_name UNIQUE (column1)
);

The SQL UNIQUE KEY constraint ensures that all values in a column are unique. It prevents duplicate values from being stored in the table. It is usually used in conjunction with the PRIMARY KEY constraint to create a composite key. The UNIQUE KEY constraint is an alternative to the PRIMARY KEY constraint.

Example

CREATE TABLE products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    UNIQUE KEY (product_name)
);

This SQL statement creates a table called "products" with four columns: id, product_name, price, and a UNIQUE KEY constraint on the product_name column. The id column is set to auto-increment and the UNIQUE KEY constraint ensures that the product_name column values are unique, meaning no two rows in the table can have the same product_name value.

Defining a Unique Key Constraint on Multiple Columns

Syntax

CREATE TABLE table_name(
    col1 datatype,
    col2 datatype,
    col3 datatype,
    ...
    CONSTRAINT constraint_name UNIQUE(col1, col2, col3, ...)
);

This statement creates a new table with the specified columns, data types, and constraint. The CONSTRAINT clause defines a unique key constraint on the specified columns, which ensures that all values in the columns are unique.

Example

CREATE TABLE students (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT pk_students UNIQUE (first_name, last_name, email)
);

The above SQL statement creates a table called 'students' with three columns: first_name, last_name, and email. It then defines a SQL unique key constraint, named 'pk_students', which ensures that the combination of values in the first_name, last_name, and email columns must be unique across the table. This means that no two students can have the same first name, last name, and email address.

SQL UNIQUE KEY Constraint on ALTER TABLE

Defining a Unique Key Constraint on Single Column

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column);

This statement adds a unique constraint to the specified table. This means that any combination of values across the specified column must be unique; no two rows can have the same values for those columns. The constraint_name is an optional name given to the constraint.

Example

ALTER TABLE people
ADD CONSTRAINT unique_name UNIQUE (first_name);

This statement adds a unique constraint to the people table so that the combination of values across the first_name must be unique. No two rows in the people table can have the same first name. The constraint is given the name unique_name.

Defining a Unique Key Constraint on Multiple Column

Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

This statement is used to add a unique constraint to a table. It specifies the name of the constraint and the columns to be included in the constraint. The UNIQUE keyword indicates that each combination of values in the specified columns must be unique. This ensures that no duplicate records can be inserted into the table.

Example

ALTER TABLE student
ADD CONSTRAINT UK_student UNIQUE (regno, name);

This statement will add a unique key constraint on the student table with the combination of the regno and name columns. This means that the combination of these two columns must be unique for every row in the table.

DROP SYNTAX FOR A FOREIGN KEY Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

This statement is used to drop an existing foreign key constraint from a table. The table_name is the name of the table where the foreign key constraint is located and the constraint_name is the name of the foreign key constraint that should be dropped.

Example

ALTER TABLE table_name
ADD CONSTRAINT FK_constraint_name
FOREIGN KEY (column_name)
REFERENCES other_table (column_name);

This statement changes the table named "table_name" and adds a foreign key constraint named "FK_constraint_name." The foreign key is placed on the column named "column_name" and it is referencing the column named "column_name" within the table named "other_table.”

Conclusion

The UNIQUE Key in SQL was a great success and allowed the company to increase the participation in its loyalty program. It also provided customers with the assurance that their loyalty rewards were safe and secure.

Key Takeaways

1. Use UNIQUE constraints to ensure that no duplicate values are entered in specific columns or groups of columns.
2. UNIQUE constraints can be defined on one or more columns of a table.
3. UNIQUE constraints apply on a single table, while PRIMARY KEY constraints apply on multiple tables.
4. When defining UNIQUE constraints, NULL values can be used but only once.
5. When defining UNIQUE constraints, duplicate values are not allowed.
6. You can use UNIQUE constraints to enforce data integrity in your database.

Quiz

1. What is the keyword used to uniquely identify a row in a database table? 

1. Primary Key 
2. Foreign Key
3.  Unique Key 
4. Alternate Key

 Answer: a. Primary Key

2. How can a Primary Key be composed? 

1. Single Column 
2. Multiple Columns
3. Combination of Single and Multiple Columns 
4. Any of the Above

Answer: d. Any of the Above

3. What type of integrity is enforced by a Unique Key? 

1. Entity Integrity 
2. Domain Integrity 
3. Referential Integrity 
4. User-Defined Integrity

 Answer: a. Entity Integrity

4. What happens if a duplicate value is entered for a Unique Key? 

1. An error is raised 
2. The row is added to the table 
3. The value is automatically changed 
4. The row is ignored

Answer: a. An error is raised