Friday, February 15, 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 genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog