Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Question Unanswered: Alter Column datatype with Default constraint

    I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

    alter my_table
    add col_1 smallint Not Null
    constraint df_my_table__col_1 default 0
    go

    I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

    alter table my_table
    alter column col_1 decimal(14,2) Not Null
    go

    Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

    Thx

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    yes thats right,

    the constraint has a dependency on the column and hence the data type of the column.

    If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

    drop the constriant, then do what you need to do to the column

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •