SQL UNIQUE constraint

The UNIQUE constraint in SQL is used to ensure that all values in a column are unique, or distinct, across all rows in a table. This constraint helps to maintain data integrity and accuracy in a database by preventing duplicate values in a specific column.

To create a table with the UNIQUE constraint, you can use the following syntax:

1CREATE TABLE table_name (
2  column1 data_type UNIQUE,
3  column2 data_type,
4  ...
5);

Add UNIQUE constraint using ALTER statement

Alternatively, you can also add the UNIQUE constraint to an existing table using the ALTER TABLE statement:

1ALTER TABLE table_name
2ADD CONSTRAINT constraint_name UNIQUE (column_name);

Here is an example of how to create a table with the UNIQUE constraint:

1CREATE TABLE Orders (
2  OrderID int UNIQUE,
3  CustomerID int,
4  ProductName varchar(255),
5  Quantity int
6);

In this example, the OrderID column has the UNIQUE constraint, which means that all values in this column must be unique across all rows in the Orders table. This ensures that each order in the table has a unique identifier.

Here is a sample table data for the Orders table with the UNIQUE constraint:

OrderID	CustomerID	ProductName	Quantity
1	10	Product A	10
2	20	Product B	5
3	30	Product C	15

If you try to insert a duplicate value in the OrderID column, the database will return an error, as the UNIQUE constraint prevents duplicate values in this column. For example:

1INSERT INTO Orders (OrderID, CustomerID, ProductName, Quantity)
2VALUES (1, 40, 'Product D', 20);

This insertion attempt will fail, as the value of 1 already exists in the OrderID column.

DROP UNIQUE constraint using ALTER statement

The SQL UNIQUE Constraint can be dropped using the ALTER TABLE statement along with the DROP CONSTRAINT clause. The syntax for dropping a UNIQUE Constraint is as follows:

1ALTER TABLE table_name
2DROP CONSTRAINT constraint_name;

where table_name is the name of the table and constraint_name is the name of the UNIQUE Constraint you want to drop.

Here's an example that demonstrates how to drop a UNIQUE Constraint named UQ_Order_CustomerID from a table named Orders:

1ALTER TABLE Orders
2DROP CONSTRAINT UQ_Order_CustomerID;

Note that, once you drop a UNIQUE Constraint, the constraint will no longer be enforced, and the column or columns that the constraint was applied to will allow duplicate values. So, you should use this statement with caution.