Post reply

update constraint

  • June 9, 2011 at 12:55 pm

    #251687

    I have 2 similar tables with a date column haveing defaultvalue to get date but when i script these table the definition looks different.

    Tab1 - [ToDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_ToDate] DEFAULT (getdate()),

    Tab2 - [ToDate] [datetime] NOT NULL DEFAULT (getdate()).

    They both might be working same but i would like to update Tab1 like Tab2, how can i do that?

  • June 9, 2011 at 2:38 pm

    #1336729

    Chances are the constraint was created as a named-default in Tab1 and an unnamed-default in Tab2. It may have gone something like this:

    CREATE TABLE dbo.Tab1

    (

    [ToDate] [datetime] NOT NULL

    CONSTRAINT [DF_Employee_ToDate] DEFAULT (GETDATE())

    ) ;

    CREATE TABLE dbo.Tab2

    (

    [ToDate] [datetime] NOT NULL

    DEFAULT (GETDATE())

    ) ;

    IMHO all constraints should be explicitly named so that DDL scripts are portable from one environment to the next, but it sounds like you want to change the constraint on Tab1 to be an unnamed-default.

    What you would do for that is:

    BEGIN TRAN

    -- drop named default

    ALTER TABLE dbo.Tab1 DROP CONSTRAINT [DF_Employee_ToDate] ;

    GO

    -- add unnamed-default

    ALTER TABLE dbo.Tab1 ADD DEFAULT (getdate()) FOR [ToDate]

    GO

    COMMIT TRAN

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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