Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    11

    Unanswered: How to Alter a column??

    Hi,
    I am new to SQL Server 2005. Please help
    what is the problem with this code..?

    ALTER TABLE AM_Master
    ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT getdate();
    Go

    The error is like

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'DEFAULT'.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    As with the CHECK constraint, the expression for a DEFAULT constraint must be included within brackets.

    Your example will then be:

    ALTER TABLE AM_Master
    ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());
    Go
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2006
    Posts
    11
    Try this:
    ALTER TABLE AM_Master
    ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());
    Go

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Indeed, as T'zozo suggested above, you may wish to name your constraints to make your physical schema easier to manage.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2007
    Posts
    11
    Thanks for your help...
    but when i used the sql above following comments were returned;

    1. ALTER TABLE AM_Master
    ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());

    Error Message
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'DEFAULT'.

    2.ALTER TABLE AM_Master
    ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());

    Error message:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'CONSTRAINT'.

    ... Could you please tell why this happens...?




    2.

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I was mistaken. It happens occasionally. It appears that you cannot actually modify a DEFAULT definition of a table column using the alter command statement.

    From the documentation:

    ALTER COLUMN

    Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

    The modified column cannot be any one of the following:

    ...

    Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

    Perhaps you can do this using Enterprise Manager.

    Regards,
    Last edited by r123456; 11-15-07 at 04:41.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Robert, Robert, Robert...
    EM / SSMS are evil

    [removed, because it was rubbish]
    Last edited by gvee; 11-15-07 at 04:43.
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I edited my post after you made that reply, which is the reason why your post doesn't make much sense to people who never read my original message, in which I stated something about using Enterprise Manager to make DDL changes rather than searching for that elusive DDL definition.

    That view came from a year or two ago when, using SQL Server, I realised that I could perform a task, relating to object manipulation, in Enterprise Manager but not using direct SQL.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE tableName (
      columnName int
        CONSTRAINT constraintName DEFAULT 1
    )
    GO
    
    ALTER TABLE tableName
      DROP CONSTRAINT constraintName
    GO
    
    ALTER TABLE tableName
      ADD CONSTRAINT constraintName DEFAULT 2
        FOR columnName
    GO
    
    DROP TABLE tableName
    GO
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •