patternsqlMinor
Can I specify a name for the index which SQL Server creates for my unique constraint?
Viewed 0 times
uniquecanthecreatesserversqlnameforconstraintwhich
Problem
In SQL Server I deploy a table with a
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
where
Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?
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__1F778345BDwhere
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.