Create constraint using index

Question

Post reply

Create constraint using index

Jo Pattyn

SSC-Dedicated

Points: 32074

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: 110206 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: 100950 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) A socialist is someone who will give you the shirt off *someone else's* back.

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

Jo Pattyn

SSC-Dedicated

Points: 32074

April 11, 2022 at 9:24 am

#4013851

Thanks for the answers.

ScottPletcher SSC Guru Points: 100950 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) A socialist is someone who will give you the shirt off *someone else's* back.