Can I specify a name for the index which SQL Server creates for my unique constraint?

Question

In SQL Server I deploy a table with a UNIQUE constraint:

CREATE TABLE [dbo].[Something] (
    [IdentityId]  UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID()
    -- Whatever else...
)

and this makes SQL Server to create a unique index to back the constraint. That index will have name generated automatically and looking something like

UQ__tmp_ms_x__1F778345BD

where UQ means "unique" and all the rest is well, random stuff to me. If I have two unique constraints for the same table they just look like two random strings which is not very convenient.

Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?

Answer 1

Referencing the documentation for Create Unique Constraints, you should be able to use something like this:

CREATE TABLE [dbo].[Something](
    [IdentityId] [uniqueidentifier] NULL,
 CONSTRAINT [MyUniqueConstraintName] UNIQUE NONCLUSTERED 
(
    [IdentityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 2

Martin Smith showed how to properly create the index with the same name as the constraint with this code:

ALTER TABLE [dbo].[tblSomeTable] 
ADD [UID] UNIQUEIDENTIFIER CONSTRAINT [DF_tblSomeTable_UID] DEFAULT NEWID() NOT NULL CONSTRAINT [UQ_tblSomeTable_UID] UNIQUE

See example code here showing how that works: https://dbfiddle.uk/1PvFqu8T

For completeness, it's possible to Rename Indexes using sp_rename, so that you can correct the index that was created with the random name. The doc states that "Renaming an index does not cause the index to be rebuilt".

USE AdventureWorks2022;  
GO  
--Renames the IX_ProductVendor_VendorID index on the Purchasing.ProductVendor table to IX_VendorID.   

EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';   
GO

Answer 3

You can name your unique (or any other) constraint in-line when creating the table:

CREATE TABLE [dbo].[Something] (
    [IdentityId]   UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT [UQ_Something_IdentityId] UNIQUE NONCLUSTERED ([IdentityId] ASC)
        CONSTRAINT [DF_Something_IdentityId] DEFAULT (NEWID())
    /* Whatever else... */
);

Or after the table's created:

ALTER TABLE [dbo].[Something] ADD [IdentityId] UNIQUEIDENTIFIER NOT NULL;
ALTER TABLE [dbo].[Something] ADD CONSTRAINT [UQ_Something_IdentityId]
    UNIQUE NONCLUSTERED ([IdentityId] ASC);
ALTER TABLE [dbo].[Something] ADD CONSTRAINT [DF_Something_IdentityId]
    DEFAULT (NEWID()) FOR [IdentityId];

Answer 4

Referencing the documentation for Unique constraints and check constraints you should be able to use something like this:

CREATE TABLE [dbo].[table-name] (

    [IdentityId] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),

    CONSTRAINT [UQ_Something_IdentityId] UNIQUE ([IdentityId])

    -- Other columns...

);

This specifies a custom name for the unique constraint in SQL Server, which sets the name of the under-the-hood unique index. Using the CONSTRAINT keyword will allow you to specify the desired name when defining the constraint.

The UNIQUE ([IdentityId]) keyword ensures the unique constraint is backed by a unique index.