How to delete unique index referenced by foreign keys?

views:

206

answers:

+2 Q:

How to delete unique index referenced by foreign keys?

I have a table, let's call it Users. This table has primary key called Id. Despite having Id as primary key (unique clustered), it has other index (unique nonclustered) on the same column(Id).

I would like to drop this constraint, but foreign keys reference this unique nonclustered index and I get The constraint ... is being referenced by table... error.

What is the best way to drop such index? Do you have any scripts that drop, do something, and recreate foreign key on specific column in specific table? There is a lot of foreign keys, so it would be nice if I could do it automatically. I could use INFORMATION_SCHEMA and other system object to extract information about these keys, but I don't want to write, what have already been written or can be done in other way.

+1 A:

The two-index approach can make sense:

The second index is probably much smaller than the clustered index, and would more easily fit into memory
The second index might include a selection of columns that benefit specific queries

For dropping the second index, you'll have to drop all foreign keys that refer to it first. Here is a link to the script I use to drop & recreate foreign keys.

Andomar 2009-12-24 10:46:13

It has one column (`ID`), no includes. For me it makes no sense.

LukLed 2009-12-24 10:49:56

It could be useful if you join this table to another one using the ID column. In that case, just joining the ID, and then look up only the matching rows might be better than the naïve approach. And, of course it would be beneficial if you do SELECT some_list FROM some_other_table WHERE some_column IN(SELECT id FROM the_table)

erikkallen 2009-12-24 10:53:44

@LukLed: A clustered index contains all columns in the table, an index on just `ID` just one column. Like erikkallen comments, a smaller index has advantages

Andomar 2009-12-24 10:59:41

@erikkallen, Andomar: So do you create additional indexes on primary keys to speed up joining? Do you do it often? I understand benefits, but does it really work much faster than clustered index? Specially when you select data from join table.

LukLed 2009-12-24 11:30:05

These days memory is so cheap that our server can fit all clustered indexes in memory. But, with our old server, we really benefitted from non-clustered primary key indexes. We would for example create an index on `id, status` so you could search for open orders in-memory

Andomar 2009-12-24 12:19:28

+1 A:

In order to drop a nonclustered index that is referenced by a foreign key you must furst drop the foreign key constraint.

Take a look at the following scrips available from a poster over at SQL Server Central. They may require some tweaking for your "exact" requirements however they provide the basis for scripting out and then subsequently rebuilding foreign keys.

Scrip out Foreign Keys

John Sansom 2009-12-24 10:49:33

This script is better than Andomar. It recreates CASCADE and SET NULL action.

LukLed 2009-12-30 13:58:16

@LukLed: Glad you approve ;-)

John Sansom 2009-12-30 19:36:43

ansaurus

tags:

views:

answers:

How to delete unique index referenced by foreign keys?

related questions