I would like to use the ALTER TABLE
command to ADD CONSTRAINT
. I do this often with no issues but in this case I need to add it as an INDEX
as well.
From the Microsoft website it shows the following...
-- ALTER TABLE options
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{
{PRIMARY KEY | UNIQUE }
{
NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
| NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
}
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
| CHECK ( logical_expression )
}
<column_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count)}
<table_index> ::=
INDEX index_name
{[ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
| [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
[ ON filegroup_name | default ]
| CLUSTERED COLUMNSTORE [WITH ( COMPRESSION_DELAY = {0 | delay [Minutes]})]
[ ON filegroup_name | default ]
}
I usually do something like the following for adding a primary key and it works great (EX#1):
ALTER TABLE [table_one]
ADD CONSTRAINT [PK_7_table_one]
PRIMARY KEY CLUSTERED ([id] ASC)
WITH(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON[PRIMARY];
The SQL Server Management Studio scripts the table using the following (EX#2):
CREATE UNIQUE NONCLUSTERED INDEX [IX_table_one]
ON [dbo].[table_one] ( [guid] ASC )
WITH (
PAD_INDEX = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
IGNORE_DUP_KEY = OFF
DROP_EXISTING = OFF
ONLINE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY]
So at the moment I'm doing the following (EX#3):
ALTER TABLE [table_one]
ADD CONSTRAINT [IX_7_table_one_guid]
UNIQUE NONCLUSTERED ([id] ASC)
WITH(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON[PRIMARY];
However, I feel like I'm missing something. Like I need to have UNIQUE NONCLUSTERED INDEX ([id] ASC)
(which I know will not work). How do I make it show "Index" like this?
The issue being, when I create my new constraint using the above method (EX#3) the results of the Type
on the constraint is a Unique Key
rather than an Index
like the previous image.
In the end, my goal is to use something like EX#3 to create the constraint using ALTER TABLE
while ending up with a Type
of Index
instead of Unique Key
.
1条答案
按热度按时间6ovsh4lw1#
In the end, my goal is to use something like EX#3 to create the constraint using ALTER TABLE while ending up with a Type of Index instead of Unique Key.
This is not possible in the Syntax for disk-based tables.
You can use
ALTER TABLE ADD <table_constraint>
to add aunique
orprimary key
constraint.Both of these are backed by indexes and you can define a lot of the possible index options but not all (no included columns or filter predicates for example).
In order to get something that shows up as type "index" it either needs to have been created at the same time as the table (as
CREATE TABLE
does support defining inline indexes) or added subsequently withCREATE INDEX
.