MySQL: Unique Constraints - TechOnTheNet

1. Home
2. MySQL

MySQL: Unique Constraints

This MySQL tutorial explains how to create, add, and drop unique constraints in MySQL with syntax and examples.

What is a unique constraint in MySQL?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in MySQL is:

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n) ); table_name The name of the table that you wish to create. column1, column2 The columns that you wish to create in the table. constraint_name The name of the unique constraint. uc_col1, uc_col2, ... uc_col_n The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in MySQL using the CREATE TABLE statement.

CREATE TABLE contacts( contact_id INT(11) PRIMARY KEY AUTO_INCREMENT, reference_number INT(11) NOT NULL, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25), birthday DATE, CONSTRAINT reference_unique UNIQUE (reference_number));

In this example, we've created a unique constraint on the contacts table called reference_unique. It consists of only one field - the reference_number field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE contacts( contact_id INT(11) PRIMARY KEY AUTO_INCREMENT, reference_number INT(11) NOT NULL, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25), birthday DATE, CONSTRAINT contact_name_unique UNIQUE (last_name, first_name));

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name The name of the table to modify. This is the table that you wish to add a unique constraint to. constraint_name The name of the unique constraint. column1, column2, ... column_n The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in MySQL using the ALTER TABLE statement.

ALTER TABLE contacts ADD CONSTRAINT reference_unique UNIQUE (reference_number);

In this example, we've created a unique constraint on the existing contacts table called reference_unique. It consists of the field called reference_number.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE contacts ADD CONSTRAINT contact_name_unique UNIQUE (last_name, first_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in MySQL is:

ALTER TABLE table_name DROP INDEX constraint_name; table_name The name of the table to modify. This is the table that you wish to remove the unique constraint from. constraint_name The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in MySQL.

ALTER TABLE contacts DROP INDEX reference_unique;

In this example, we're dropping a unique constraint on the contacts table called reference_unique.

NEXT: Indexes Share on:

Databases

• SQL
• Oracle / PLSQL
• SQL Server
• MySQL
• MariaDB
• PostgreSQL
• SQLite

MS Office

• Excel
• Access
• Word

Web Development

• HTML
• CSS
• JavaScript
• Color Picker

Programming

• C Language

More

• ASCII
• Unicode
• Linux
• UNIX
• Techie Humor

MySQL Basics

• ALIASES
• AND
• AND & OR
• BETWEEN
• COMPARISON OPERATORS
• DELETE
• DELETE LIMIT
• DISTINCT
• EXISTS
• FROM
• GROUP BY
• HAVING
• IN
• INSERT
• INTERSECT
• IS NOT NULL
• IS NULL
• JOIN
• LIKE
• NOT
• OR
• ORDER BY
• SELECT
• SELECT LIMIT
• SUBQUERY
• TRUNCATE
• UNION
• UNION ALL
• UPDATE
• WHERE

MySQL Advanced

• Alter Table
• AUTO_INCREMENT
• Change Password
• Comments in SQL
• Create Table
• Create Table As
• Create User
• Data Types
• Declare Variables
• Drop Table
• Drop User
• Find Users
• Find Users Logged In
• Functions
• Grant/Revoke Privileges
• Indexes
• Literals
• Primary Key
• Procedures
• Rename User
• Show Grants
• Unique Constraints
• Views

MySQL Cursors

• Close Cursor
• Cursor NOT FOUND
• Declare Cursor
• Fetch Cursor
• Open Cursor

MySQL Loops/Conditionals

• CASE
• IF-THEN-ELSE
• ITERATE
• LEAVE
• LOOP
• REPEAT Loop
• RETURN
• WHILE Loop

MySQL Triggers

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Drop Trigger

String Functions

• ASCII
• CHAR_LENGTH
• CHARACTER_LENGTH
• CONCAT
• CONCAT_WS
• FIELD
• FIND_IN_SET
• FORMAT
• INSERT
• INSTR
• LCASE
• LEFT
• LENGTH
• LOCATE
• LOWER
• LPAD
• LTRIM
• MID
• POSITION
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SPACE
• STRCMP
• SUBSTR
• SUBSTRING
• SUBSTRING_INDEX
• TRIM
• UCASE
• UPPER

Numeric/Math Functions

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• AVG
• CEIL
• CEILING
• COS
• COT
• COUNT
• DEGREES
• DIV
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG
• LOG10
• LOG2
• MAX
• MIN
• MOD
• PI
• POW
• POWER
• RADIANS
• RAND
• ROUND
• SIGN
• SIN
• SQRT
• SUM
• TAN
• TRUNCATE

Date/Time Functions

• ADDDATE
• ADDTIME
• CURDATE
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• CURTIME
• DATE
• DATE_ADD
• DATE_FORMAT
• DATE_SUB
• DATEDIFF
• DAY
• DAYNAME
• DAYOFMONTH
• DAYOFWEEK
• DAYOFYEAR
• EXTRACT
• FROM_DAYS
• HOUR
• LAST_DAY
• LOCALTIME
• LOCALTIMESTAMP
• MAKEDATE
• MAKETIME
• MICROSECOND
• MINUTE
• MONTH
• MONTHNAME
• NOW
• PERIOD_ADD
• PERIOD_DIFF
• QUARTER
• SEC_TO_TIME
• SECOND
• STR_TO_DATE
• SUBDATE
• SUBTIME
• SYSDATE
• TIME
• TIME_FORMAT
• TIME_TO_SEC
• TIMEDIFF
• TIMESTAMP
• TO_DAYS
• WEEK
• WEEKDAY
• WEEKOFYEAR
• YEAR
• YEARWEEK

Advanced Functions

• BIN
• BINARY
• CASE
• CAST
• COALESCE
• CONNECTION_ID
• CONV
• CONVERT
• CURRENT_USER
• DATABASE
• IF
• IFNULL
• ISNULL
• LAST_INSERT_ID
• NULLIF
• SESSION_USER
• SYSTEM_USER
• USER
• VERSION

Encryption Functions

• ENCRYPT
• MD5
• OLD_PASSWORD
• PASSWORD

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2025 TechOnTheNet.com. All rights reserved.