|
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
|
