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?

Answer 1

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