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 2008 Forums
 Transact-SQL (2008)
 Foreign Key Table index - drop or disable

Author  Topic 

audiudew
Starting Member

5 Posts

Posted - 2014-10-31 : 17:14:20
As a follow up to this question ["Remove Primary Key from ID column"][http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=196998], I need to modify the data type (from INT to BIGINT) in a table that contains a Foreign Key column. I am unable to do so, because I get this error message:

Msg 5074, Level 16, State 1, Line 1
The index 'indexname' is dependent on column 'ItemID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ItemID failed because one or more objects access this column.

Should I drop the index, then do ALTER TABLE, ALTER COLUMN and then recreate the INDEX or should I disable the index?

I need to maintain the records that are currently in the table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 17:18:08
You can't modify the data type when it's an identity column. You will need to create another table with the structure you want, move the data, drop the old table and then rename the new table to your table name. These steps were described in the other topic you linked.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

audiudew
Starting Member

5 Posts

Posted - 2014-10-31 : 17:20:11
I just modified an identity column (for the primary table and maintained the record count) dropping the PK constraint, ALTER TABLE --> ALTER COLUMN ColumnName BIGINT...... then added back the PK constraint. Now I am looking to do the same for the linked tables (that have FK constraint).
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 17:23:37
Oh oops! I thought that wasn't allowed. Drop all of the dependent objects on the columns to change the data types.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -