4

I have a table dbo.ExceptionMessage and now I want to change the column datatype nvarchar(100) to nvarchar(MAX). I used alter query for changing this 

ALTER TABLE dbo.ExceptionMessage ALTER COLUMN   Address nvarchar(MAX)

and while excecuting this query it shows some error like.

The object 'DF_ExceptionMessage_Address' is dependent on column 'Address'.

ALTER TABLE ALTER COLUMN Address failed because one or more objects access this column.

How can we solve this...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sreekala C
  • 75
  • 1
  • 8
  • possible duplicate of [How to change column datatype in SQL Server database without losing data](http://stackoverflow.com/questions/5136013/how-to-change-column-datatype-in-sql-server-database-without-losing-data) – RAS Jan 10 '14 at 07:00
  • That dupe doesn't appear to help here. OP is already using the sql in the answer from that question, the key was he needed to drop the dependent constraints. – OGHaza Jan 10 '14 at 14:35

4 Answers4

7

First Delete all Constraint Like this 

ALTER TABLE TableName DROP CONSTRAINT [DF__TableName__ColumnName__FieldName]

and then perform change

ALTER TABLE dbo.ExceptionMessage ALTER COLUMN   Address nvarchar(MAX)

then re enter the constraints

Amit Bisht
  • 4,870
  • 14
  • 54
  • 83
1

You have to find out which type of constraint DF_ExceptionMessage_Address is, drop it, alter the column type and then re-create the constraint if you need it.

0

you try first:

ALTER TABLE <tablename> DROP CONSTRAINT <Con_Name>;

And Then Do your Alter

ALTER TABLE dbo.ExceptionMessage ALTER COLUMN   Address nvarchar(MAX)

Again Add Constraint

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
0

thiz help you to alter

   alter table TableName 

     alter column ColumnName nvarchar(200);
shylesh krishna
  • 86
  • 9