Meritshot Tutorials
- Home
- »
- SQL UNIQUE Constraint
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL UNIQUE Constraint
The UNIQUE constraint in SQL ensures that all values in a column (or a group of columns) are distinct across all rows in the table. This means that no two rows can have the same value for the specified column(s). This constraint is useful for maintaining data integrity and ensuring that key attributes are unique.
Syntax
CREATE TABLE table_name (
column_name datatype UNIQUE,
…
);
— For multiple columns
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
UNIQUE (column1, column2)
);
- column_name: The name of the column to which the UNIQUE constraint is applied.
- datatype: The type of data that the column will store (e.g., INT, VARCHAR).
Example:
1 Creating a Table with a UNIQUE Constraint
Suppose you want to create a table called Customers where the Email column must have unique values:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FullName VARCHAR(100),
Email VARCHAR(100) UNIQUE,
PhoneNumber VARCHAR(15)
);
In this table, the Email column cannot have duplicate values across the Customers table.
2 Adding a UNIQUE Constraint to an Existing Column
If you have an existing table Employees and want to ensure that the PhoneNumber column contains only unique values:
ALTER TABLE Employees
ADD CONSTRAINT unique_phone UNIQUE (PhoneNumber);
This command adds a UNIQUE constraint to the PhoneNumber column, ensuring no two employees can have the same phone number.
3 Creating a Composite UNIQUE Constraint
If you want to ensure that the combination of FirstName and LastName in the Employees table is unique:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
UNIQUE (FirstName, LastName)
);
This command ensures that no two rows can have the same combination of FirstName and LastName.
4 Handling Unique Constraint Violations
If you try to insert a duplicate value into a column with a UNIQUE constraint:
INSERT INTO Customers (CustomerID, FullName, Email, PhoneNumber)
VALUES (1, ‘Rajesh Kumar’, ‘rajesh.kumar@example.com’, ‘9876543210’);
If rajesh.kumar@example.com already exists in the table, this command will result in an error due to the UNIQUE constraint on the Email column.
Tips to Remember
- Enforce Data Integrity: Use UNIQUE constraints to prevent duplicate data and maintain data integrity.
- Consider Composite Keys: You can apply UNIQUE constraints to multiple columns to ensure that combinations of values are unique.
- Handling Errors: When inserting or updating data, handle errors related to unique constraints by checking for existing values.
Frequently Asked Questions
Q1: Can a column with a UNIQUE constraint have NULL values?
A1: Yes, a column with a UNIQUE constraint can have multiple NULL values, as NULL is considered a distinct value.
Q2: Can I have multiple UNIQUE constraints on a single table?
A2: Yes, you can apply multiple UNIQUE constraints on different columns or combinations of columns within a table.
Q3: What happens if I try to insert a duplicate value into a column with a UNIQUE constraint?
A3: SQL will return an error, preventing the insertion or update of a row that would violate the uniqueness of the column.
Q4: How do I check which columns have UNIQUE constraints?
A4: Use a DESCRIBE or SHOW INDEX command to view the constraints and indexes applied to columns in a table.
Q5: Can I remove a UNIQUE constraint once it’s applied?
A5: Yes, you can drop a UNIQUE constraint using an ALTER TABLE command.
ALTER TABLE Customers
DROP CONSTRAINT unique_phone;