none
alter column to default value

    Question

  • i wan to alter a column to default value of getdate()

    existing table has column

    column1 DateTime null

    Friday, June 01, 2012 6:09 PM

Answers

  • One thing to be aware of is that a default does not mean that it will override an explicitly provided NULL value.

    Consider a table that looks like this

    column1 int

    column2 int

    column3 datetime NULL DEFAULT GETDATE()

    INSERT TABLE (column1, column2, column3) VALUES (1,2,NULL)

    will insert a null into column3 even if there is a default declared

    INSERT TABLE (column1, column2) VALUES (1,2)

    Would cause the default to come into effect for column 3 because an insert is being done without providing an explicit value for column 3


    Chuck Pedretti | Magenic – North Region | magenic.com


    Friday, June 01, 2012 6:27 PM

All replies

  • Like this:

    ALTER TABLE dbo.blah ADD CONSTRAINT
    	DF_blah_column1 DEFAULT getdate() FOR column1


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed as answer by C. Pfeiffer Friday, June 01, 2012 6:15 PM
    Friday, June 01, 2012 6:12 PM
  • i get below error

    ALTER TABLE dbo.DTF_Transaction_Details_Staging_Table
    ADD CONSTRAINT Load_Date  DEFAULT getdate() FOR Load_Date

    Msg 1781, Level 16, State 1, Line 2
    Column already has a DEFAULT bound to it.
    Msg 1750, Level 16, State 0, Line 2
    Could not create constraint. See previous errors.

    Friday, June 01, 2012 6:18 PM
  • Then you already have a default declared on that column.  To see what it is you can do this:

    SELECT b.name AS TABLE_NAME, d.name AS COLUMN_NAME, a.name AS CONSTRAINT_NAME, c.text AS DEFAULT_VALUE FROM sys.sysobjects a INNER JOIN (SELECT name, id FROM sys.sysobjects WHERE xtype = 'U') b on (a.parent_obj = b.id) INNER JOIN sys.syscomments c ON (a.id = c.id) INNER JOIN sys.syscolumns d ON (d.cdefault = a.id) WHERE a.xtype = 'D'

    AND b.name = 'DTF_Transaction_Details_Staging_Table' ORDER BY b.name, a.name

    -----



    Chuck Pedretti | Magenic – North Region | magenic.com

    Friday, June 01, 2012 6:22 PM
  • One thing to be aware of is that a default does not mean that it will override an explicitly provided NULL value.

    Consider a table that looks like this

    column1 int

    column2 int

    column3 datetime NULL DEFAULT GETDATE()

    INSERT TABLE (column1, column2, column3) VALUES (1,2,NULL)

    will insert a null into column3 even if there is a default declared

    INSERT TABLE (column1, column2) VALUES (1,2)

    Would cause the default to come into effect for column 3 because an insert is being done without providing an explicit value for column 3


    Chuck Pedretti | Magenic – North Region | magenic.com


    Friday, June 01, 2012 6:27 PM