SQL UNIQUE Constraint
UNIQUE constraint in SQL is used to ensure that the values in a specific column or set of columns are distinct, i.e., no duplicates are allowed. When you create a
UNIQUE constraint, the database engine automatically creates a unique index on the column(s) specified in the constraint.
You can add a
UNIQUE constraint to a column or set of columns in a table by using the
ALTER TABLE statement, like so:
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2);
Alternatively, you can add a
UNIQUE constraint while creating a new table, using the
CREATE TABLE statement:
CREATE TABLE table_name ( column1 data_type UNIQUE, column2 data_type );
You can also specify more than one
UNIQUE constraint for a table or you can combine multiple column to create unique constraint on the combination of columns.
CREATE TABLE table_name ( column1 data_type, column2 data_type, column3 data_type, UNIQUE (column1, column2), UNIQUE (column2, column3) );
You can use the following command to drop a previously created
UNIQUE constraint by the name of the constraint.
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
UNIQUE constraint in SQL is used to ensure that the data in a specific column or set of columns is unique across all rows in a table. Here's an example of how to create a
UNIQUE constraint on the
CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) NOT NULL, UNIQUE (email) );
In this example, the
UNIQUE constraint is applied to the
users table can have the same email address.
You can also specify multiple columns for a
UNIQUE constraint, like this:
CREATE TABLE orders ( order_id INT PRIMARY KEY, item_id INT NOT NULL, customer_id INT NOT NULL, UNIQUE (item_id, customer_id) );
UNIQUE constraint is applied to the combination of values in the
customer_id columns, so no two rows in the
orders table can have the same combination of values in those two columns.
You can also add a unique constraint to an existing table with
ALTER TABLE statement
ALTER TABLE users ADD UNIQUE (email);
This statement will add unique constraint on the email column in users table.
There are a few best practices to keep in mind when working with
UNIQUE constraints in SQL:
UNIQUEconstraints instead of
PRIMARY KEYconstraints when you have multiple unique columns in a table, or when you want to enforce unique values across multiple columns.
Understand the difference between
UNIQUEconstraint allows multiple NULL values, while a
PRIMARY KEYconstraint does not.
UNIQUEconstraints on columns that are frequently used in joins, as it helps to ensure the integrity of the data and also improves the performance of your queries.
When you are adding a
UNIQUEconstraint to an existing table, make sure to check for any duplicate values that may already exist in the table. If there are duplicates, you will need to either remove them or update them before adding the constraint.
Be aware of the indexing that happens in the background, when you define a
UNIQUEconstraint on a column, a
UNIQUEindex is created automatically and while this could increase performance, it can have an impact on disk space.