Sql-server – SQL Azure: Drop Constraint and Index

azure-sql-databaseconstraintindexsql server

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!

Best 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.

Related Question