views:

171

answers:

4

Hello,

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

+1  A: 

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.

Oded
Not always true. If there is already a clustered index on the table, the PRIMARY KEY constraint will be enforced by a unique nonclustered index.
Frank Kalis
In SQL Server 2008 foreign keys have something in common with indexes. I couldn't drop an index (and it was not primary key index), because if was referenced by foreign keys.
LukLed
@LukLed, when you have a 1 to many parent-child relationship enforced with a FK, the parent needs to be unique, if you try to drop the uniqueness constraint on the parent, it will throw an error.
KM
@KM: So foreign keys have something to do with indexes. And foreign keys are directly connected to an index. If you have two unique indexes on the same column and create foreign key, specific one will be blocked by foreign key.
LukLed
So...does dropping the PK constraint drop the index automatically?
Seth Spearman
The primary key enforcing index is not necessarily the clustered index. It usually is, but is not required. Primary keys can constraints can be enforced just as well using a non clustered index.
Remus Rusanu
@Seth Spearman: If you drop a PRIMARY KEY constraint, the underlying index will be dropped as well. No matter if clustered or not.@Remus: "Primary keys can constraints"? I think you meant "Primary keys and constraints".
Frank Kalis
+1  A: 

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.

KM
+1  A: 

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.

Frank Kalis
+1  A: 

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.

Thomas