HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
uniquecanthecreatesserversqlnameforconstraintwhich

Problem

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?

Solution

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]

Code Snippets

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]

Context

StackExchange Database Administrators Q#196234, answer score: 7

Revisions (0)

No revisions yet.