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.

The syntax looks OK to me. What DB2 version and OS are you using?

Andy

Db2 8.2 on windows.

That explains it. You cannot set NOT NULL in v8.2.

DB2 Universal Database


Andy

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.

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

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.

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

You could/should consider moving to a newer version. V8 is out of support for several years now.