April 6, 2022 at 11:06 am
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
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
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".
April 11, 2022 at 9:24 am
Thanks for the answers.
April 11, 2022 at 3:18 pm
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".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy