Post reply

how to drop a conatraint on column with default value

  • September 29, 2016 at 10:01 am

    #400810

    I have a table with all data inserted , i don't want to lose that data, but need to alter the column which has default value

    this is column defination to my original table

    [EmailDate] [datetime] NOT NULL CONSTRAINT [DF_OnlineDay10_EmailDate_GETDATE] DEFAULT (CONVERT([date],getdate(),0))

    Need to change to

    [EmailDate] [datetime] NOT NULL CONSTRAINT [DF_OnlineDay10_EmailDate_GETDATE] DEFAULT getdate()

    Need to fix this ASAP , please help to resolve this issue.

  • September 29, 2016 at 10:14 am

    #1903985

    Drop the default and recreate it.

    CREATE TABLE SampleData(

    [EmailDate] [datetime] NOT NULL CONSTRAINT [DF_OnlineDay10_EmailDate_GETDATE] DEFAULT (CONVERT([date],getdate(),0))

    );

    INSERT INTO SampleData VALUES(DEFAULT);

    ALTER TABLE SampleData DROP CONSTRAINT [DF_OnlineDay10_EmailDate_GETDATE] ;

    ALTER TABLE SampleData ADD CONSTRAINT [DF_OnlineDay10_EmailDate_GETDATE] DEFAULT getdate() FOR EmailDate;

    INSERT INTO SampleData VALUES(DEFAULT);

    SELECT * FROM SampleData;

    DROP TABLE SampleData;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 29, 2016 at 10:37 am

    #1903991

    Thank you. I got it resolved.

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

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