Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    40

    Unanswered: Set Column type not null-- Giving error.

    Hello,
    I have a table with 1000 records,I just added a varchar column and refreshed data in that column.Now I am trying add a not null constraint to that column so that I can make it part of the unique key but it is throwing an error:

    Here is the syntax for the column:
    ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME SET NOT NULL;
    ----------------------
    Error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "NOT" was found following "R COLUMN SRC_TYP
    SET". Expected tokens may include: "DEFAULT". SQLSTATE=42601

    SQL0104N An unexpected token "NOT" was found following "R COLUMN SRC_TYP SET". Expected tokens may include: "DEFAULT ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601

    -------------------------------------------

    Can someone tell me where the issue is?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The syntax looks OK to me. What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Aug 2010
    Posts
    40
    Db2 8.2 on windows.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That explains it. You cannot set NOT NULL in v8.2.

    DB2 Universal Database


    Andy

  5. #5
    Join Date
    Aug 2010
    Posts
    40
    ok ...but my actual requirement is to add this new column that I added today to an existing Unique constraint.
    For that purpose, I have dropped the existing Unique constraint and tried to recreate with this new column as part of it..But I am getting error saying that I cannot add a column to a unique if it is not defined not null.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will have to add the column with NOT NULL WITH DEFAULT ''. Then fill in the data the way you want it, then ALTER TABLE ALTER COLUMN DROP DEFAULT.

    Andy

  7. #7
    Join Date
    Aug 2010
    Posts
    40
    Anyways ...This is what I did...REnamed the table,Then dropped all foriegn Key Constraints,Created new table with that name and reloaded data back into the table
    and put back the FK constraints. Tough though,any other solution.

  8. #8
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by ARWinner View Post
    You will have to add the column with NOT NULL WITH DEFAULT ''. Then fill in the data the way you want it, then ALTER TABLE ALTER COLUMN DROP DEFAULT.

    Andy
    Yeah by mistake I have added the column without the Default value.Anyways thanks for all your help.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Rajesh1203 View Post
    Anyways ...This is what I did...REnamed the table,Then dropped all foriegn Key Constraints,Created new table with that name and reloaded data back into the table
    and put back the FK constraints. Tough though,any other solution.
    You could/should consider moving to a newer version. V8 is out of support for several years now.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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