User Tools

Site Tools


notes:sql:unique

UNIQUE constraint

A unique constraint is implemented as a unique index.

Examples:

-- Customers must have unique names
CREATE UNIQUE INDEX INDEX_Customer_CustomerName ON Customer(CustomerName)
 
-- A non-unique, non-clustered index on a foreign key (to improve performance of JOINs)
CREATE INDEX INDEX_Order_CustomerId ON [ORDER](CustomerID) WITH FILLFACTOR = 90
 
-- A unique, non-cluctered, composite index
CREATE UNIQUE INDEX INDEX_Order_Code ON [ORDER](OrderCode, OrderSubcode) ON [PRIMARY]

Define a unique constraint using CREATE TABLE:

CREATE TABLE TestTable
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
 
    -- Named unique constraint
    TestField CHAR(3) NOT NULL CONSTRAINT INDEX_TestTable_Field UNIQUE NONCLUSTERED,
 
    -- Table-level unique constraint
    CodeID INT NOT NULL DEFAULT 0,
    SubCodeID INT NOT NULL DEFAULT 0,
    CONSTRAINT INDEX_TestTable_Code UNIQUE NONCLUSTERED(CodeID, SubCodeID),
)
GO

Define a unique constraint using ALTER TABLE:

ALTER TABLE TestTable WITH CHECK ADD CONSTRAINT UN_TestTable_TestField UNIQUE(TestField)
notes/sql/unique.txt · Last modified: 2020/08/26 (external edit)