none
ALTER TABLE ALTER COLUMN (To set the default value) RRS feed

  • Pergunta

  • I am using the below command to update the default binding value for a datetime column:

    ALTER

     

    TABLE [dbo].[Orders] ALTER COLUMN OrderCreated DATETIME NULL DEFAULT (getdate())

    but I am getting this error: Incorrect syntax near the keyword 'DEFAULT'.

    Plz do let me know what is the problem here. Thanks.
    quinta-feira, 26 de agosto de 2010 11:43

Respostas

  • One more time - you need to find the name of the existing constraint and drop it first. Only then you will be able to create it.

    I can do the homework and google all necessary commands (I don't remember off the top of my head), but you can google the same way I do.

    The main point is - without DROPPING the original constraint you will not be able to change it.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Sugerido como Resposta Cees van Diermen quinta-feira, 26 de agosto de 2010 13:44
    • Marcado como Resposta KJian_ quarta-feira, 1 de setembro de 2010 08:43
    quinta-feira, 26 de agosto de 2010 13:30
    Moderador

Todas as Respostas

  • ALTER TABLE dbo.Table1 ADD CONSTRAINT
     DF_Table1_col DEFAULT getdate() FOR col

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    quinta-feira, 26 de agosto de 2010 11:51
    Usuário que responde
  • When I see the create script for the table below is code I get:

    CREATE TABLE [dbo].[Orders](
    OrderCreated] NULL DEFAULT (getutcdate())

    Now, I want to set the DEFAULT datetime to (getdate())

    SQL command I am using is as below:
    ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Timestamp] DEFAULT getdate() FOR OrderCreated

    But I still get the below error, what is wrong here ...
    Column already has a DEFAULT bound to it
    Could not create constraint. See previous errors.

    quinta-feira, 26 de agosto de 2010 13:05
  • When I see the create script for the table below is code I get:

    CREATE TABLE [dbo].[Orders](
    OrderCreated] NULL DEFAULT (getutcdate())

    Now, I want to set the DEFAULT datetime to (getdate())

    SQL command I am using is as below:
    ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Timestamp] DEFAULT getdate() FOR OrderCreated

    But I still get the below error, what is wrong here ...
    Column already has a DEFAULT bound to it
    Could not create constraint. See previous errors.

    Then you need to drop and recreate or alter the constraint
    -ankur
    quinta-feira, 26 de agosto de 2010 13:07
  • You need to first drop existing constraint and then you will be able to create a different new default constraint. Google on 

    DROP CONSTRAINT T-SQL

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    quinta-feira, 26 de agosto de 2010 13:08
    Moderador
  • I am trying to change the default binding value on an existing table, as you can see from the Create Script the table does not have constraint for me to DROP the same:

    CREATE TABLE [dbo].[Orders](
    OrderCreated] NULL DEFAULT (getutcdate())

    quinta-feira, 26 de agosto de 2010 13:13
  • I am trying to change the default binding value on an existing table, as you can see from the Create Script the table does not have constraint for me to DROP the same:

    CREATE TABLE [dbo].[Orders](
    OrderCreated] NULL DEFAULT (getutcdate())

    Even if you are creating a new table you need to use ADD Constraint syntax..

    And if table is already present then you need to add constraint.

    And if table is already present and a constraint already attached to the same column then you need to drop the ol constraint and add constraint again.


    -ankur
    quinta-feira, 26 de agosto de 2010 13:16
  • The table is already existing and does have a default binding value associated with a column which I want to change and it does not have any constraint on it, so when I use the below code:

    ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Timestamp] DEFAULT getdate() for OrderCreated

    I still get the below error:

    Column already has a DEFAULT bound to it.

    quinta-feira, 26 de agosto de 2010 13:26
  • One more time - you need to find the name of the existing constraint and drop it first. Only then you will be able to create it.

    I can do the homework and google all necessary commands (I don't remember off the top of my head), but you can google the same way I do.

    The main point is - without DROPPING the original constraint you will not be able to change it.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Sugerido como Resposta Cees van Diermen quinta-feira, 26 de agosto de 2010 13:44
    • Marcado como Resposta KJian_ quarta-feira, 1 de setembro de 2010 08:43
    quinta-feira, 26 de agosto de 2010 13:30
    Moderador
  • Thanks I got it working the thing is when I checked the Create script of the table I could not see the constraint in it but then under Constraints I did found one. Thanks Again ...
    quinta-feira, 26 de agosto de 2010 13:34

  • ALTER TABLE dbo.Orders ADD  CONSTRAINT [DF_Orders_OrderCreated]  DEFAULT ('N') FOR  OrderCreated 
    sexta-feira, 13 de dezembro de 2019 03:34