 |
SQL Operations: UNIQUE |
|
|
|
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)
|
|