Unique Constraint VS Unique Index

While recently reviewing my previous article on SQL Server Performance Tuning, I noticed that the prefixes for indexes were incomplete. After referring to this article, "The prefixes of indexes and constraints you need to understand (AK, PK, IX, CK, FK, DF, UQ)", I have added the following:

• Primary Key: PK_TableName.
• Clustered Index: CX_TableName_Column1_Column2.
• Non-Clustered Index: IX_TableName_Column1_Column2.
• Unique Index (Alternate Key): AK_TableName_Column1_Column2.
• Unique Constraint: UQ_TableName_Column1_Column2.
• Check Constraint: CK_TableName_Column1_Column2.
• Default Constraint: DF_TableName_Column1_Column2.
• Foreign Key: FK_TableName1_Column1_Column2_TableName2.

After these additions, I realized that both Unique Constraint and Unique Index are related to data uniqueness.

In terms of naming, a Constraint is used to ensure data integrity, while an Index is used for query performance. However, in practice, a Unique Index can also ensure data uniqueness.

According to the article "Create Unique Constraints", when a Unique Constraint is created, a Unique Index is created simultaneously.

So, what is the point of creating a Unique Constraint? There is a claim online that a Foreign Key can only reference a Unique Constraint. After all, one of the error messages for a failed Foreign Key creation is "The columns in table ... do not match an existing primary key or UNIQUE constraint."

Both Constraints and Unique Indexes can work successfully.

CREATE TABLE [dbo].[Main](
 [Id] [uniqueidentifier] NOT NULL,
 [SeqNo] [bigint] IDENTITY(1,1) NOT NULL,
 [UQ] [bigint] NULL,
 [AK] [bigint] NULL,
    CONSTRAINT [PK_Main] PRIMARY KEY NONCLUSTERED (
     [Id] ASC
    ) WITH (
     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY],
    CONSTRAINT [UQ_Main] UNIQUE NONCLUSTERED (
     [UQ] ASC
    ) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [MainId] [uniqueidentifier] NOT NULL,
 [RefSeqNo] [bigint] NOT NULL,
 [RefUQ] [bigint] NULL,
 [RefAK] [bigint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main] FOREIGN KEY([MainId])
REFERENCES [dbo].[Main] ([Id])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main_AK] FOREIGN KEY([RefAK])
REFERENCES [dbo].[Main] ([AK])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main_AK]
GO
ALTER TABLE [dbo].[Ref]  WITH CHECK ADD  CONSTRAINT [FK_Ref_Main_UQ] FOREIGN KEY([RefUQ])
REFERENCES [dbo].[Main] ([UQ])
GO
ALTER TABLE [dbo].[Ref] CHECK CONSTRAINT [FK_Ref_Main_UQ]
GO

Furthermore, excerpting from this article, "Create Unique Indexes", in SQL Server, a Unique Constraint is primarily more semantically explicit.

There is no significant difference between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same way, and the query optimizer does not differentiate between a unique index created by a constraint or manually. However, creating a UNIQUE constraint on a column makes the index objective clearer.

Creating Unique Constraint and Unique Index

Creating with SQL

To create a Unique Constraint using SQL, replace {} with the appropriate content.

ALTER TABLE {TableName} ADD CONSTRAINT {IndexName} UNIQUE {ColumnName});
CREATE UNIQUE INDEX {IndexName} ON {TableName} ({ColumnName});

-- Example: ALTER TABLE Main ADD CONSTRAINT UQ_Main_UQ UNIQUE (UQ);

To create a Unique Index using SQL, replace {} with the appropriate content.

CREATE UNIQUE INDEX {IndexName} ON {TableName} ({ColumnName});

-- Example: CREATE UNIQUE INDEX AK_Main_AK ON Main (AK);

Creating with SSMS

1. Open the table designer, right-click and select [Indexes/Keys].
2. Choose settings as needed:
   • Unique Constraint:
     • Type: Unique Key.
     • Is Unique: Yes (this will be selected automatically and displayed in gray, unable to be changed).
   • Unique Index:
     • Type: Index.
     • Is Unique: Yes.

TIP

If you are simply creating an index, you can also right-click the "Indexes" folder and select "New Index". Note that if the designer is open, "New Index" will be grayed out and unavailable.

SSMS Display

How do you know if you created a Unique Constraint or a Unique Index?

In the SSMS display, the "Keys" folder shows indexes related to constraints, such as Primary Key, Unique Constraint, and Foreign Key. The "Indexes" folder displays all indexes except for Foreign Keys.

Change Log

• 2024-07-25 Initial document creation.