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 · 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

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

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

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

Examples related to sql-server-2008

Examples related to alter-table

Examples related to alter-column