Post reply

Create constraint using index

  • 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?*/

  • April 6, 2022 at 12:16 pm

    #4011581 

    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

  • April 6, 2022 at 8:25 pm

    #4011815

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 11, 2022 at 9:24 am

    #4013851

    Thanks for the answers.

  • April 11, 2022 at 3:18 pm

    #4014000

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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