Home

SQL Operations: UNIQUE

 

Introduction

The UNIQUE keyword can be applied to an index to make sure that each record is unique. During data entry, this would make sure records do not have the same value for a certain column.

Here is an example:

-- =============================================
-- Create index basic template
-- =============================================
USE Exercise1;
GO

CREATE TABLE dbo.Employees
(
    EmployeeNumber integer NOT NULL, 
    FirstName varchar(50) NULL,
    LastName varchar(50) NOT NULL,
    HourlySalary decimal(6, 2) 
)
GO

CREATE UNIQUE INDEX IDX_EmployeeNumber
ON dbo.Employees(EmployeeNumber);
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(92935, N'Joan', N'Hamilton', 22.50)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(28405, N'Patricia', N'Katts', 24.50)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(22940, N'Peter', N'Malley', 14.25)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(29075, N'Peter', N'Mukoko', 30.15)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(27495, N'Christine', N'Fink', 32.05)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(22940, N'Gertrude', N'Monay', 15.55)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(20285, N'Helene', N'Mukoko', 26.65)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(50026, N'Leonie', N'Crants', 18.75)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
		      LastName, HourlySalary)
VALUES(95274, N'James', N'Watts', 12.55)
GO

This would produce:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Employees' 
with unique index 'IDX_EmployeeNumber'.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
 
     
 

Home Copyright © 2009-2013 FunctionX, Inc.