So I add a new column to an existing table, and I create it using a command like this:
ALTER TABLE table_name
ADD TagUploader bit not null default 0;
but I then I change my mind, the datatype should be tinyint instead of bit, so I try to alter the table again using this line:
ALTER TABLE table_name
ALTER COLUMN TagUploader tinyint;
but this error message was shown in the message frame:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__PROFILE__tagUplo__6CA31EA0' is dependent on column 'TagUploader'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN TagUploader failed because one or more objects access this column.
then I tried to drop the column, still appear a message like this:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__PROFILE__tagUplo__6CA31EA0' is dependent on column 'TagUploader'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN TagUploader failed because one or more objects access this column.
So the problem is that I create a default value at the creation of that column, that creates a constrain to the table, you can find it in the Constrains folder inside the table's tree
have tried to delete it by right clicking it, then choose delete but an error like this appears:
So I guess the major problem is the constrain, so I search a way to drop the constrain, and yes there is a way, the line was like this:
ALTER TABLE docsadm.profile
DROP CONSTRAINT [DF__PROFILE__tagUplo__6CA31EA0]
GO
After running that script, it will show this message:
Command(s) completed successfully.
Then after that, you can alter the column or even drop the column without any problem anymore. So what I learned from this case is that default value can create a constrain to the table, if you don't need it, just release it.
-thats all folks, says bugs bunny :P-
