ALTER TABLE on dependent column

sql-server-2008 alter-table alter-column
Milee picture Milee · Mar 26, 2012 · Viewed 166.9k times · Source

I am trying to alter column datatype of a primary key to tinyint from int.This column is a foreign key in other tables.So,I get the following error:

Msg 5074, Level 16, State 1, Line 1 The object 'PK_User_tbl' is dependent on column 'appId'. Msg 5074, Level 16, State 1, Line 1 The object 'FK_Details_tbl_User_tbl' is dependent on column 'appId'. Msg 5074, Level 16, State 1, Line 1 The object 'FK_Log_tbl_User_tbl' is dependent on column 'appId'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN appId failed because one or more objects access this column.

Is there any other way other than to delete dependencies and recreate them?

Answer

Justin Pihony picture Justin Pihony · Mar 26, 2012

I believe that you will have to drop the foreign key constraints first. Then update all of the appropriate tables and remap them as they were.

ALTER TABLE [dbo.Details_tbl] DROP CONSTRAINT [FK_Details_tbl_User_tbl];
-- Perform more appropriate alters
ALTER TABLE [dbo.Details_tbl] ADD FOREIGN KEY (FK_Details_tbl_User_tbl) 
    REFERENCES User_tbl(appId);
-- Perform all appropriate alters to bring the key constraints back

However, unless memory is a really big issue, I would keep the identity as an INT. Unless you are 100% positive that your keys will never grow past the TINYINT restraints. Just a word of caution :)

Related questions

Alter column, add default constraint

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More
SQL Server add auto increment primary key to existing table

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null). I'm assuming I can run a query to fill this column with incremental numbers, and then …

Read More