Friday, 15 February 2013

How to use UNIQUE Constraint in SQL Server

SQL Server UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table.The UNIQUE and PRIMARY KEY constraints both provides uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Creating a UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "C_Id" column when "Customers" table is created:
SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (C_Id)
)

To define a UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_CustomerID UNIQUE (C_Id,LastName)
)

Creating a UNIQUE Constraint with ALTER TABLE Statement
To create a UNIQUE constraint on the "C_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD UNIQUE (C_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT uc_CustomerID UNIQUE (C_Id,LastName)

To DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Customers DROP CONSTRAINT uc_CustomerID

MySQL:
ALTER TABLE Customers DROP INDEX uc_CustomerID

Thanks, TAMATAM


No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts