DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a UNIQUE constraint?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for demonstrating UNIQUE constraint creation.

 

-- SQL Server Create UNIQUE Constraints - SQL UNIQUE

USE tempdb;

GO

CREATE TABLE Celebrity( CelebrityID int identity(1,1) PRIMARY KEY,

Name varchar(64), -- Name varchar(64) UNIQUE,

ModifiedDate date default(getdate()));

GO

 

-- Create unique constraint

ALTER TABLE dbo.Celebrity ADD CONSTRAINT

      IX_Celebrity UNIQUE NONCLUSTERED

      (

         Name

      )

GO

 

-- Populate table

INSERT Celebrity (Name) VALUES

('Alicia Silverstone'),

('Frank Sinatra'),

('Halle Berry'),

('Elvis Presley'),

('Gwen Stefani')

GO

-- (5 row(s) affected)

 

-- Populate table with duplicate

INSERT Celebrity (Name) VALUES

('Gwineth Paltrow'),

('Tom Jones'),

('Madonna'),

('Elvis Presley'),

('Sofia Milos')

GO

/*

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'IX_Celebrity'. Cannot insert duplicate key in

object 'dbo.Celebrity'.

The statement has been terminated.

*/

------------

Related article:

Unique Constraints and Unique Indexes

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE