1. SQL Tutorial
  2. SQL UNIQUE Contraints

SQL UNIQUE Contraints

Submitted by admin on Wednesday, April 27, 2011 - 13:33.

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

  1.  
  2. CREATE TABLE TABLE_NAME
  3.   (
  4.   column_name1 data_type,
  5.   column_name2 data_type,
  6.   column_name3 data_type,
  7.   UNIQUE (column_name1)
  8.   ....
  9.   )

UNIQUE Contraints Syntax - SQL Server / Oracle / MS Access

  1. CREATE TABLE TABLE_NAME
  2.   (
  3.   column_name1 data_type UNIQUE,
  4.   column_name2 data_type,
  5.   column_name3 data_type,
  6.   ....
  7.   )

MySQL Example

  1. CREATE TABLE Users
  2.   (
  3.   ID INT,
  4.   Firstname text,
  5.   Lastname text,
  6.   Salary FLOAT,
  7.   DeptID INT,
  8.   UNIQUE (ID)
  9.   )

SQL Server / Oracle / MS Access Example

  1. CREATE TABLE Users
  2.   (
  3.   ID INT UNIQUE,
  4.   Firstname text,
  5.   Lastname text,
  6.   Salary FLOAT,
  7.   DeptID INT
  8.   )

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

  1. ALTER TABLE Users
  2. ADD UNIQUE (ID)

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

MySQL / SQL Server / Oracle / MS Access

  1. ALTER TABLE Users
  2. 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

  1. ALTER TABLE Users
  2. DROP INDEX uc_ID

SQL Server / Oracle / MS Access

  1. ALTER TABLE Users
  2. DROP CONSTRAINT uc_ID
Tags
sql tutorial
UNIQUE Contraints

Add new comment

About text formats
  • 115 views