SQL Unique
Duplicate values are not allowed in the columns to which the UNIQUE constraint is applied. The column with the unique constraint will always contain a unique value. This constraint can be applied to one or more than one column of a table, which means more than one unique constraint can exist on a single table. Using the UNIQUE constraint, you can also modify the already created tables.
Example:
CREATE TABLE TableName (ColumnName1 datatype UNIQUE, ColumnName2 datatype,…., ColumnNameN datatype);
The following SQL creates a UNIQUE constraint on the “ID” column when the “Persons” table is created:
CREATE TABLE Persons (
ID int UNIQUE,
LastName varchar(255) ,
FirstName varchar(255),
Age int
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int ,
LastName varchar(255) ,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
To create a UNIQUE constraint on the “ID” column when the table is already created, use the following SQL:
ALTER TABLE Persons ADD UNIQUE (ID);
ALTER TABLE Persons ADD CONSTRAINT CH_UNIQUE UNIQUE (ID);
To drop a UNIQUE constraint, use the following SQL:
ALTER TABLE Persons DROP CONSTRAINT CH_UNIQUE;
Course Video
1.create a table with one unique constrain with 5 column includes id, name, age, email, address and phone number and also insert sample data in the table
Hint: Add Unique constrain on ‘id’ column
2.create table with two unique constrain
Hint: Add Unique constrain on ‘name’ and ‘age’ column
3.alter unique constrain from above table and add the unique constraint to the phone number column
4.Drop unique constrain
5.Create new table and add unique constrain for combination of two column
Hint: Add unique constrain on email and age
Note: If you try to insert the similar value in the unique key column you will get the error
6. Construct a SQL script to define a table named “Client” with a set of columns including “id” (uniquely identifying each record), “name”, “age”, “email”, “address”, and “phone_number”. Enforce a unique constraint on the “id” column. Subsequently, populate the “Client” table with sample data comprising three records.
7. Create a table named “Customer_Order” with the following columns: id, customer_id, order_date, total_amount, and status. Add unique constraints on the “customer_id” and “order_date” columns. Insert sample data into the “Customer_Order” table with five records.
8. Alter the unique constraint and add a new unique constraint to the “ID” column in the “Customer_Order” table.
9. “Create a table named ‘Student’ with the following columns: Student_ID (not null), First_Name, Last_Name, and Email. Add a unique constraint to the ‘Email’ column to ensure that each student has a unique email address. Insert 5 records into the ‘Student’ table with various first names, last names, and email addresses.”
10. “Assuming you have a table named ‘Students’ with columns: Student_ID, First_Name, Last_Name, and Email, and there’s a unique constraint on the ‘Email’ column. Write an SQL query to remove the unique constraint from the ‘Email’ column.”
YouTube Reference :
1) SQL Unique in Hindi/Urdu
2) SQL Unique in English
The SQL UNIQUE constraint ensures that all values in a column or group of columns are unique, preventing duplicate entries.
- Primary Key: Ensures uniqueness and cannot contain NULL values.
- UNIQUE Constraint: Allows one or more NULL values (depending on the database system).
Yes, you can define a UNIQUE constraint on multiple columns to ensure that the combination of values in those columns is unique. Example:
sqlCopy codeCREATE TABLE Orders ( order_id INT, product_id INT, UNIQUE (order_id, product_id) );
You can add a UNIQUE constraint during table creation or by altering an existing table:
- During Table Creation:
sqlCopy codeCREATE TABLE Users ( user_id INT, email VARCHAR(255) UNIQUE );
- Alter Existing Table:
sqlCopy codeALTER TABLE Users ADD CONSTRAINT unique_email UNIQUE (email);
The database throws an error, indicating a violation of the UNIQUE constraint.
Yes, our website offers a detailed SQL UNIQUE Constraint Tutorial with examples and best practices.
Yes, you can drop a UNIQUE constraint using:
sqlCopy codeALTER TABLE table_name DROP CONSTRAINT constraint_name;
- Adding UNIQUE to a column containing duplicate values.
- Forgetting to account for NULL values if allowed by the database system.
The UNIQUE constraint ensures no duplicate entries, helping maintain the accuracy and consistency of data.
Yes, a table can have multiple UNIQUE constraints on different columns or combinations of columns.
