Create constraint using index

Question

Post reply

Create constraint using index

Jo Pattyn

SSC-Dedicated

Points: 32345

April 6, 2022 at 11:06 am

#4011574

Is there a way in T-SQL to use an existing index as source for a constraint?

Goal:

Have a constraint with an included column

In Oracle it is possible with "using index"

CREATE TABLE MYTEST
(
ID number(1,0) not null
,ID2 number(1,0) not null
)
;
CREATE INDEX IX_ID2 ON MYTEST (ID2,ID);

ALTER TABLE MYTEST ADD CONSTRAINT UC_ID2 UNIQUE (ID2) USING INDEX IX_ID2;

CREATE TABLE #IndexTEST
(
ID int not null
,ID2 int not null
)

CREATE UNIQUE INDEX IX_ID2 ON  #IndexTEST(ID2) INCLUDE (ID);

ALTER TABLE #IndexTEST ADD CONSTRAINT C_R_T UNIQUE (ID2) /*use index IX_ID2?*/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

CREATE UNIQUE INDEX IX_ID2 ON  #IndexTEST(ID2) INCLUDE (ID);

creates unique constraint, exactly as you requested.

Second statement is not needed at all.

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

I don't believe SQL will use an existing index for a constraint, even if the index perfectly matches the constraint to be added.

In your situation, the index and the constraint don't match anyway -- at least not in SQL Server -- therefore you'd need both the index and the constraint for your conditions.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 3

Jo Pattyn

SSC-Dedicated

Points: 32345

April 11, 2022 at 9:24 am

#4013851

Thanks for the answers.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

Sergiy wrote:

CREATE UNIQUE INDEX IX_ID2 ON  #IndexTEST(ID2) INCLUDE (ID);
creates unique constraint, exactly as you requested.
Second statement is not needed at all.

Technically that's just a unique index, not a unique "constraint". Unique constraints in SQL Server cannot have INCLUDEd column(s).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".