ALTER TABLE on dependent column

Question

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

User · Accepted Answer

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 :)

User · Answer

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

User · Answer

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

[sql-server-2008] ALTER TABLE on dependent column

Examples related to sql-server-2008

Examples related to alter-table

Examples related to alter-column