Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    11

    Question Unanswered: How to change default value for field by query?

    Hello.

    I wrote:

    ALTER TABLE tblActions ALTER COLUMN ActionTitle NVARCHAR(255) DEFAULT ''

    But got an error near the 'DEFAULT' word.
    How to do it correctlly?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    cuz you aint seeting the default to anything. i think you just want to DROP CONSTRAINT.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2009
    Posts
    11
    Not to waste much of my live, give me, plz, the good example of changing the default value for column (to '' or something else).

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You cannot alter constraints, you must drop and re-create them.


    To create a default constraint:
    Code:
    ALTER TABLE <table_name>
    ADD
       CONSTRAINT <constraint_name> DEFAULT ('') FOR <column_name>
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2009
    Posts
    11
    Maby thanks.

    But, while trying:
    ALTER TABLE tblActions ADD CONSTRAINT CK_ActionTitle DEFAULT 'q' FOR ActionTitle

    I got the error:
    Column already has a DEFAULT bound to it.

    How to find out the name of the existent constraint to delete it before adding new?

    I can't find "DROP CONSTRAINT" in MSSQLServer2000 BOL.
    I need my code to be working in MS SQL 2000 - 2008 DBMS, is it posible within the task of changing default value for column?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    works on SQL2005+ only
    Code:
    DECLARE @schema_name sysname
          , @table_name  sysname
          , @column_name sysname
    
    SELECT @schema_name = 'dbo'
         , @table_name  = 'test'
         , @column_name = 'id'
    
    SELECT d.name
    FROM   sys.schemas As s
     INNER
      JOIN sys.tables As t
        ON s.schema_id = t.schema_id
     INNER
      JOIN sys.columns As c
        ON c.object_id = t.object_id
     INNER
      JOIN sys.default_constraints As d
        ON d.parent_object_id = c.object_id
       AND d.parent_column_id = c.column_id
    WHERE  s.name = @schema_name
    AND    t.name = @table_name
    AND    c.name = @column_name
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ALTER TABLE <table_name>
      DROP CONSTRAINT <constraint_name>
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2009
    Posts
    11
    Many thanks.
    All was done.
    What about the analog of this wonderful query for MS SQL 2000?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No idea; I don't know how the sysobjects (type='D') relates to syscolumns. I can tell you what table the constraint is in, but not what column it applies to...
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2009
    Posts
    2
    Quote Originally Posted by sv2k View Post
    Many thanks.
    All was done.
    What about the analog of this wonderful query for MS SQL 2000?
    sp_help 'tablename' will show default constraint names

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus View Post
    cuz you aint seeting the default to anything. i think you just want to DROP CONSTRAINT.

    You even type DRUNK
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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