Alter Table by adding a NOT NULL and UNIQUE KEY

mjshaheed · Posted: Tue Oct 23, 2007 2:14 pm

Hi All,

I want to modify an existing field by adding a NOT NULL and UNIQUE KEY to an existing field. Can you please give me the syntax?

For example, suppose if the field is ENO CHAR(03) and i want to add both NOT NULL and UNIQUE KEY to it.

Thanks,
Shahul

Srihari Gonugunta · Posted: Tue Oct 23, 2007 3:57 pm

Hi,
You can use the following statements.
Alter table your_table add unique (ENO);
Alter table your_table alter column ENO SET NOT NULL;

mjshaheed · Posted: Tue Oct 23, 2007 4:39 pm

Hi,

I tried the above two statements in QMF as well as SPUFI. But it is throwing some error. Please find the error status i got from SPUFI.
And the coulumn ENO is already existing in the table.

********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
ALTER TABLE EMPLLL ALTER COLUMN ENO SET NOT NULL; 00000200
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "NOT". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: CYCLE, NOCYCLE, ORDER, NOCACHE, NOORDER, NOMINVALUE,
NOMAXVALUE

Thanks,
shahul

Srihari Gonugunta · Posted: Tue Oct 23, 2007 4:52 pm

Shahul,
I suppose you are working on DB2 version 8 or less than that. only version 9 supports that syntax. Sorry for not mentioning the same.
You can probably add a check constraint to acheive the same effect in V8.

Srihari Gonugunta · Posted: Tue Oct 23, 2007 5:22 pm

you can try this out.
ALTER TABLE EMPLLL
ADD CONSTRAINT C1 CHECK (ENO IS NOT NULL)

mjshaheed · Posted: Wed Oct 24, 2007 12:13 pm

Thank You Friend,

It worked great. Btw can you please tell me how to find out the DB2 version?

Regards,
Shahul

Srihari Gonugunta · Posted: Wed Oct 24, 2007 12:44 pm

Fine Shahul.

Issue the DB2 command
-DIS GROUP