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.
| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-02-15 : 12:45:15
|
| Here we go again:I have a database with indexes scattered all over different file groups. 9 to be precise.I first tried to drop them and then recreate them on a primary file group or at worst a secondary filegroup only for indexes. But some of these indexes are foreign keys to other tables hence they won't get dropped.When I looked at the SSMS, it gives me an option to change the file group, or so it seems.It says on the tool: "You can allow online processing of DML statements while moving a clustered index to another filegroup or partition scheme" But when I tried to move one such index to another file group, it says, "Table 'tblName' already has a primary key defined on it.Could not create constraint. See previous errors. (Microsoft SQL Server, Error: 1779)"Is there a shorter way to move all these (expletives) indexes without having to go through the graveyard shift?any help/script/macro will be helpful. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-16 : 08:31:24
|
| "some of these indexes have foreign key constraints"Is this some new feature in SQL 2005 ?ThanksKristen |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-02-16 : 10:24:54
|
quote:
Originally posted by Kristen
"some of these indexes have foreign key constraints"Is this some new feature in SQL 2005 ?ThanksKristen
Sorry Kristen, my bad, you caught me there.It should read these Primary Keys are Foreign keys to other tables.I will correct it . |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-16 : 12:33:58
|
| I would script the FKs and PKs, drop he FKs, Drop and Recreate the PKs (in the desired File Group), and then recreate the FKs.Best not to have any users connected at the time so that they cannot create any data that violates the FKs (or PKs) whilst they are "absent"Kristen |
|
|
|
|
|
|
Index Issues