Not able to drop the index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Not able to drop the index

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-05-08 : 12:36:04

Here is my code,

1. create table t1 (id int )
2. create unique index u on t1(id)

3. create table t2 (id int constraint f foreign key references t1(id))

4. ALTER TABLE t2 NOCHECK CONSTRAINT ALL
5. ALTER TABLE t1 NOCHECK CONSTRAINT ALL

6. drop index t1.u

I get a FK voilation exception. on line 6.
I was not expecting this, because I am doing 4 & 5

Is dropping the FK is only option?

Actually I am trying to move the unique index to a different file group.

------------------------
I think, therefore I am - Rene Descartes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 13:04:19

Yes you have to drop the FK.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-05-08 : 13:12:36

I believe you can use the Alter index command in SQL 2005 to move the index to a new filegroup.

EDIT: You will have to use the REBUILD option, which is similar tot he old DBCC DBREINDEX command.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-05-08 : 13:13:56

Thank you Tara.

------------------------
I think, therefore I am - Rene Descartes

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-05-08 : 13:17:02

quote:
Originally posted by mcrowley

I believe you can use the Alter index command in SQL 2005 to move the index to a new filegroup.

EDIT: You will have to use the REBUILD option, which is similar tot he old DBCC DBREINDEX command.

Are you sure of this?
Because I saw this in BOL

ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order. Use CREATE INDEX with the DROP_EXISTING clause to perform these operations.

------------------------
I think, therefore I am - Rene Descartes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-08 : 13:22:03

Thats true. You don't have to rebuild index for this scenerio.

Not 100% sure,if you go properties of your unique index -storage- and you can change filegroup there i guess.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-05-08 : 13:24:03

quote:
Originally posted by sodeep
Not 100% sure,if you go properties of your unique index -storage- and you can change filegroup there i guess.

..can't do that. I have hundreds of indexes....

------------------------
I think, therefore I am - Rene Descartes

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-05-08 : 13:50:11

Sodeep is correct. alter index REBUILD does not allow for moving the index pages.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources