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 |
|
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 1The index 'indexname' is dependent on column 'ItemID'.Msg 4922, Level 16, State 9, Line 1ALTER 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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). |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Foreign Key Table index - drop or disable