Alter column add default constraint

Question

I have  a table and one of the columns is  Date  of type datetime  We decided to add a default constraint to that column  Alter table TableName alter column dbo TableName Date default getutcdate      but this gives me error      Incorrect syntax near       Does anyone see anything obviously wrong here  which I am missing  other than having a better name for the column

User · Answer

You re specifying the table name twice  The ALTER TABLE part names the table  Try  Alter table TableName alter column  Date  default getutcdate

User · Answer

I use the stored procedure below to update the defaults on a column   It automatically removes any prior defaults on the column  before adding the new default   Examples of usage   -- Update default to be a date  exec  dbo   AlterDefaultForColumn    dbo   TableName    Column   getdate     -- Update default to be a number  exec  dbo   AlterDefaultForColumn    dbo   TableName    Column  6   -- Update default to be a string  Note extra quotes  as this is not a function  exec  dbo   AlterDefaultForColumn    dbo   TableName    Column     MyString       Stored procedure   SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO  -- Sample function calls  --exec  dbo   AlterDefaultForColumn    dbo   TableName    ColumnName   getdate     --exec  dbol   AlterDefaultForColumn    dbo   TableName    Column  6   --exec  dbo   AlterDefaultForColumn    dbo   TableName    Column     MyString     create PROCEDURE  dbo   ColumnDefaultUpdate                -- Table name  including schema  e g    dbo   TableName            TABLE NAME VARCHAR 100            -- Column name  e g   ColumnName            COLUMN NAME VARCHAR 100           -- New default  e g     MyDefault    or  getdate            -- Note that if you want to set it to a string constant  the contents         -- must be surrounded by extra quotes  e g     MyConstant    not  MyConstant           NEW DEFAULT VARCHAR 100        AS  BEGIN            -- Trim angle brackets so things work even if they are included      set  COLUMN NAME   REPLACE  COLUMN NAME               set  COLUMN NAME   REPLACE  COLUMN NAME                print  Table name       TABLE NAME      print  Column name       COLUMN NAME      DECLARE  ObjectName NVARCHAR 100      SELECT  ObjectName   OBJECT NAME  default object id   FROM SYS COLUMNS     WHERE  object id    OBJECT ID  TABLE NAME  AND  name     COLUMN NAME       IF  ObjectName  lt  gt          begin         print  Removed default       ObjectName          --print  ALTER TABLE      TABLE NAME     DROP CONSTRAINT      ObjectName          EXEC  ALTER TABLE      TABLE NAME     DROP CONSTRAINT      ObjectName      end      EXEC  ALTER TABLE      TABLE NAME     ADD  DEFAULT       NEW DEFAULT      FOR      COLUMN NAME      --print  ALTER TABLE      TABLE NAME     ADD  DEFAULT       NEW DEFAULT      FOR      COLUMN NAME      print  Added default of       NEW DEFAULT  END   Errors this stored procedure eliminates  If you attempt to add a default to a column when one already exists  you will get the following error  something you will never see if using this stored proc    -- Using the stored procedure eliminates this error  Msg 1781  Level 16  State 1  Line 1 Column already has a DEFAULT bound to it  Msg 1750  Level 16  State 0  Line 1 Could not create constraint  See previous errors

User · Answer

you can wrap reserved words in square brackets to avoid these kinds of errors   dbo TableName  Date

User · Answer

Actually you have to Do Like below Example  which will help  to Solve the Issue     drop table ABC table  create table ABC table       names varchar 20       age int    ALTER TABLE ABC table ADD CONSTRAINT MyConstraintName DEFAULT  This is not NULL  FOR names  insert into ABC age  values 10   select   from ABC

User · Answer

I confirm like the comment from JohnH  never use column types in the your object names  It s confusing  And use brackets if possible  Try this  ALTER TABLE  TableName  ADD  DEFAULT  getutcdate    FOR  Date

User · Answer

Try this  alter table TableName   add constraint df ConstraintNAme   default getutcdate   for  Date    example  create table bla  id int   alter table bla add constraint dt bla default 1 for id    insert bla default values  select   from bla   also make sure you name the default constraint  it will be a pain in the neck to drop it later because it will have one of those crazy system generated names   see also How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server

User · Answer

alter table TableName  drop constraint DF TableName WhenEntered  alter table TableName  add constraint DF TableName WhenEntered  default getutcdate   for WhenEntered

[sql-server] Alter column, add default constraint

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to alter-table

Examples related to alter-column