Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When dropping a constraint will the supporting indexes also be dropped?

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

If so, when you use the ALTER TABLE DROP CONSTRAINT syntax...are the supporting indexes automatically dropped as well? Can these implicit supporting indexes be explicitly dropped? If so is the CONSTRAINT automatically removed?

I am just wanting to know how it works "under the covers". Googling has not helped. I imagine I could have queried some sys tables to discover the truth but thought I would try here instead.

Thanks for your help.

Seth

like image 468
Seth Spearman Avatar asked Feb 18 '10 20:02

Seth Spearman


People also ask

What happens to indexes when table is dropped?

Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible. All indexes and triggers associated with a table are dropped. All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable).

Can indexes be dropped?

To drop an index, the index must be contained in your schema, or you must have the DROP ANY INDEX system privilege. Some reasons for dropping an index include: The index is no longer required. The index is not providing anticipated performance improvements for queries issued against the associated table.

Does dropping a table drop foreign key constraint?

Dropping Tables/Schemas/Databases For example, when dropping a database, if the database contains a primary/unique key which is referenced by a foreign key from another database, the referencing foreign keys are also dropped.

Can we drop a constraints?

To drop an existing constraint, specify the DROP CONSTRAINT keywords and the identifier of the constraint. To drop multiple constraints on the same table, the constraint names must be in comma-separated list that is delimited by parentheses. The constraint that you drop can have an ENABLED, DISABLED, or FILTERING mode.


4 Answers

When you add a primary key, a unique index is in fact added. Whether that addition caused the new index to be clustered depends on whether you specified that it be non-clustered or not. If in adding a primary key constraint, you do not specify that it is clustered nor non-clustered, it will be clustered if a clustered constraint or index does not already exist on the table otherwise it will be non-clustered.

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

When you drop a constraint, any indexes created as a result of the constraint creation will be dropped. However, if you attempt to drop a unique or primary key constraint and there are foreign key constraints that reference it, you will get an error.

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

like image 66
Thomas Avatar answered Oct 03 '22 09:10

Thomas


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

Dropping a primary key constraint will also drop the underlying index.

A foreign key constraint will not add an index.

Dropping a foreign key constraint will do nothing to an index.

Foreign keys have nothing to do with indexes.

like image 39
Oded Avatar answered Oct 03 '22 09:10

Oded


FKs don't automatically get an index in SQL Server, if you want one you need to add it! When dropping the FK, you don't drop an index, you'll need to drop the index on its own.

like image 21
KM. Avatar answered Oct 03 '22 08:10

KM.


The index enforcing a UNIQUE constraint will be dropped, one index supporting a FK constraint will not be dropped automatically. It won't be created automatically either.

like image 40
Frank Kalis Avatar answered Oct 03 '22 08:10

Frank Kalis