March 26, 2009 at 6:54 am
Hello,
I have a table having unique nonclustered index made up of composite key.
I added new column in this table. This column has null values.
If I want to add this new column in composite key, it gives me following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Index 'IX_TABLE_A'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An explicit DROP INDEX is not allowed on index 'dbo.TABLE_A.IX_TABLE_A'. It is being used for FOREIGN KEY constraint enforcement. (Microsoft SQL Server, Error: 3723)
Please tell me, how can I add a new column in existing index madeup of composite key.
Thanks in advance.
April 21, 2009 at 1:25 am
As stated in the error message, you cannot drop an index that is being referenced by a foreign key constraint.
You need to drop all constraints referencing the index before you can recreate it, and after you've altered the index you should recreate the foreign key constraints to preserve relational integrity. Of course, if you're modifying an index that is referenced from other tables, you will most probably have to alter those tables as well.
I think you should provide more information (e.g. table DDL), so that we can understand your problem a little better. It would be best if you describe the situations using business terms (what you need), rather than talk about the technical aspect of the data model.
Most importantly, you haven't said anything yet as to *why* you're making these changes. Also, why are you including nillable columns in a composite key?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply