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?

score 88 · Accepted Answer · edited Jun 02 '17 at 01:19

88

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

edited Jun 02 '17 at 01:19

Bruno Oliveira

87
1
9

answered Mar 26 '12 at 05:25

Justin Pihony

66,056
18
147
180

3

In SSMS you'll find handy to right click on index and choose "Create script for index > Generate code for INSERT statement in > ..." – Marco Marsala Feb 09 '16 at 11:51
You rock! I wouldn't have sleeped tonight without you – dijam Sep 05 '16 at 18:44
Right clicking on table >> Design and then updating the type worked for me. – meekash55 Jan 25 '21 at 07:32
@meekash55 designer is wanting to drop the entire table and rebuild, just be careful if you've enabled "prevent saving w/ table recreates" – Poat Oct 12 '22 at 19:28

score 39 · Answer 2 · edited May 23 '17 at 10:31

39

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.

edited May 23 '17 at 10:31

Community

1
1

answered Mar 16 '13 at 06:57

Serj Sagan

28,927
17
154
183

1

Just to clarify - the DROP CONSTRAINT command has to be executed as a query on the table in Visual Studio on the data context there (not in SQL MS on the actual SQL DB). (I had to find this out the hard way, being a noob to this stuff, so I figured other people might find this clarification useful). – Andy Jun 15 '15 at 12:09
@Andarta: thanks a lot for your warn, but what is 'data context' in sql server management studio? I've the situation where a constraint disallow me to alter a column, but I cannot drop the constraint because SQLSERVER tell me that it do not exists ! – realtebo Jul 18 '18 at 07:54
@realtebo - the 'data context' refers to the data connection context that you see in the server explorer in Visual Studio. So rather than trying to drop the constraint in SSMS, try doing it in visual studio instead. – Andy Jul 19 '18 at 15:46

score 0 · Answer 3 · edited Feb 11 '19 at 19:31

0

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.

edited Feb 11 '19 at 19:31

Daniel

9,491
12
50
66

answered Feb 11 '19 at 19:27

Santosh

1

ALTER TABLE on dependent column

3 Answers3

Linked