Post reply

default column alter

  • February 19, 2002 at 8:52 am

    #78044

    How do I alter the columns datatype which has default on it.

    Eg. I want to change float(53) to numeric(18,6), the column has default 0

    I tried this -

    Alter Table mwebExpense

    Alter Column Exp_Reported_FXRate Numeric(18,6)

    go

    but it fails with error

    Server: Msg 4922, Level 16, State 1, Line 1

    ALTER TABLE ALTER COLUMN Exp_Reported_FXRate failed because DEFAULT CONSTRAINT DF__mwebExpen__Exp_R__7D439ABD accesses this column.

    thanks

    Sonali

  • February 19, 2002 at 9:56 am

    #424128

    From SQL Books Online ALTER COLUMN cannot be used on a column associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

    You have to drop the constraint first then add back after the change. The following example will show how to do from T-SQL.

    CREATE TABLE [tbl_15Min] (

    [T0] [tinyint] NOT NULL CONSTRAINT [DF_tbl_15Min_T0] DEFAULT (0),

    ) ON [PRIMARY]

    GO

    ALTER TABLE [tbl_15Min] DROP CONSTRAINT DF_tbl_15Min_T0

    GO

    ALTER TABLE [tbl_15Min]

    ALTER COLUMN [T0] [int]

    GO

    ALTER TABLE [tbl_15Min] ADD CONSTRAINT

    DF_tbl_15Min_T0 DEFAULT 0 FOR T0

    GO

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply