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?
This question is related to
sql-server-2008
alter-table
alter-column
The answer is
you can drop the Constraint which is restricting you. If the column has access to other table. suppose a view is accessing the column which you are altering then it wont let you alter the column unless you drop the view. and after making changes you can recreate the view.
If your constraint is on a user type, then don't forget to see if there is a Default Constraint, usually something like DF__TableName__ColumnName__6BAEFA67, if so then you will need to drop the Default Constraint, like this:
ALTER TABLE TableName DROP CONSTRAINT [DF__TableName__ColumnName__6BAEFA67]
For more info see the comments by the brilliant Aaron Bertrand on this answer.
Source: Stackoverflow.com