Skip to main content

SQL UNIQUE Contraints

Body

The UNIQUE Contraints identifies a record in a database table uniquely. Unlike PRIMARY KEY contraints, you can have many UNIQUE contraint in a table.

UNIQUE Contraints Syntax - MySQL

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, UNIQUE (column_name1) .... )

UNIQUE Contraints Syntax - SQL Server / Oracle / MS Access

CREATE TABLE table_name ( column_name1 data_type UNIQUE, column_name2 data_type, column_name3 data_type, .... )

MySQL Example

CREATE TABLE Users ( ID Int, Firstname text, Lastname text, Salary float, DeptID int, UNIQUE (ID) )

SQL Server / Oracle / MS Access Example

CREATE TABLE Users ( ID Int UNIQUE, Firstname text, Lastname text, Salary float, DeptID int )

Users

ID Firstname Lastname Salary DeptID

Now we cannot insert any user if we reassign or reuse the id already present in the table since the ID column is not unique for each user.

To ALTER TABLE

If you have created the table already, use the following syntax

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Users ADD UNIQUE (ID)

You can also define a name for the UNIQUE contrainsts, create contraints on multiple columns.

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Users ADD CONSTRAINT uc_ID UNIQUE (ID, Lastname)

To DROP a UNIQUE Constraint

If you can create or alter table with contraints, you can also drop it.

MySQL

ALTER TABLE Users DROP INDEX uc_ID

SQL Server / Oracle / MS Access

ALTER TABLE Users DROP CONSTRAINT uc_ID

Add new comment