Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.

Here is my question: When dropping Constraints, are their indexes dropped as well?

Basically, I'm asking because I want to modify an existing constraint. I want to change the foreign key to another column.

Here was the original query to create them:

-- Creating foreign key on [Industry_Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([Industry_Id])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([Industry_Id]);
GO

Here is what I'm thinking of doing:

IF OBJECT_ID(N'[dbo].[FK_CourseIndustry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_CourseIndustry];
GO

-- Creating foreign key on [IndustryId] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([IndustryId])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([IndustryId]);
GO

Is that enough and will it work? Honestly, I don't mind if it's not an elegant solution just so long as it works.

Any piece of advise or information would be highly appreciated. Thanks!

share|improve this question
add comment

1 Answer

When dropping Constraints, are their indexes dropped as well

Yes. And No. Constraints that are implemented by an index (eg. Primary key constraint and Unique constraint) will drop the index that implements them. In fact, the only way to drop the index that implements a constraint is to drop the constraint. Other constraints do not have an index to speak of, so there is nothing to drop.

You give an example of a foreign key constraint and ask if dropping the constraint will drop the index. a foreign key constraint is not implemented by an index therefore it has no index to drop. What you're tangentially hinting at is that to efficiently check a foreign key on update/on delete options, an index is required on the referencing key. It is up to you to create, drop and alter this index as appropriate to keep it aligned with the foreign key. The system will not update the index definition for you, since is unaware that the index is in any way related to your foreign key.

Is that enough and will it work?

We really can't answer it. Will creating an index on IndustryId help the FK_CourseIndustry foreign key constraint? Yes. Will your upgrade/migration script always work, be idempotent and handle all cases? Unlikely. it depends on your current schema, on how you run your migration, etc. For instance, is not at all clear if you drop the Industry_Id column or not by the time you run the upgrade in the example.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.