SQL Unique

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.”