When a constraint is reset, will supporting indexes be also canceled?

Question

I am trying to remember some sql syntax and I got the ALTER TABLE ADD CONSTRAINT syntax. I believe that I am right when I say that when you use this syntax to add a FOREIGN KEY or PRIMARY KEY constraint, this SQL server automatically creates indexes to support the constraint operations. (Is it true ... or is it true only on PC, but not on FK?)

If so, when you use the ALTER TABLE DROP CONSTRAINT syntax ... are support indexes supported automatically? Can these implicit supporting indexes be explicitly reset? If so, is CONSTRAINT automatically deleted?

I just want to know how this works under the covers. Googling did not help. I suppose I could query some sys tables to find out the truth, but thought I'd try here.

Thank you for your help.

Set

Answer 1

When adding a primary key, a unique index is added. Whether this addition is a new cluster of a new index depends on whether it was indicated that it was nonclustered or not. If when adding a primary key constraint you do not indicate that it is clustered or non-clustered, it will be clustered if the cluster constraint or index does not already exist in the table, otherwise it will be non-clustered.

When you add a foreign key, the index is not created automatically.

When dropping a constraint, any indexes created as a result of creating the constraint will be deleted. However, if you try to remove a unique or primary key constraint, and there are foreign key constraints that reference it, you will receive an error message.

Indexes created as a result of creating a constraint cannot be deleted using DROP INDEX.

Answer 2

A primary key constraint will add a clustered index to the table if it does not already exist, otherwise a unique nonclustered index will be created for it.

Reducing the primary key constraint will also lower the underlying index.

A foreign key constraint will not add an index.

Removing a foreign key constraint will do nothing for the index.

Foreign keys have nothing to do with indexes.

Answer 3

FKs do not automatically get an index in SQL Server if you want to add it! When dropping the FK, you are not dropping the index, you will need to drop the index yourself.

Answer 4

An index that provides a UNIQUE constraint will be discarded; one index that supports the FK constraint will not be automatically discarded. It will also not be created automatically.