UNIQUE Constraint/Index :
Both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness.
Unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL,however only one null value is allowed per column.
--Example for unique index to allow multiple NULL CREATE TABLE Unique_Duplicate (Col1 INT,Col2 VARCHAR(100)) GO CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 IS NOT NULL GO INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL') INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL') GO SELECT * FROM Unique_Duplicate GO
---------------------------------------------------------
--Example Unique index to allow one particular duplicate value
--Example Unique index to allow one particular duplicate value
DROP INDEX Unq_Nci_idx ON Unique_Duplicate GO CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 <> 1 GO INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value') INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value') GO SELECT * FROM Unique_Duplicate GO --DROP TABLE Unique_Duplicate
No comments:
Post a Comment