Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Alter Table by adding a NOT NULL and UNIQUE KEY

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mjshaheed

New User


Joined: 09 Apr 2007
Posts: 25
Location: Chennai,India

PostPosted: Tue Oct 23, 2007 2:14 pm    Post subject: Alter Table by adding a NOT NULL and UNIQUE KEY
Reply with quote

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
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Oct 23, 2007 3:57 pm    Post subject:
Reply with quote

Hi,
You can use the following statements.
Alter table your_table add unique (ENO);
Alter table your_table alter column ENO SET NOT NULL;
Back to top
View user's profile Send private message
mjshaheed

New User


Joined: 09 Apr 2007
Posts: 25
Location: Chennai,India

PostPosted: Tue Oct 23, 2007 4:39 pm    Post subject: Alter Table by adding a NOT NULL and UNIQUE KEY
Reply with quote

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
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Oct 23, 2007 4:52 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Oct 23, 2007 5:22 pm    Post subject:
Reply with quote

you can try this out.
ALTER TABLE EMPLLL
ADD CONSTRAINT C1 CHECK (ENO IS NOT NULL)
Back to top
View user's profile Send private message
mjshaheed

New User


Joined: 09 Apr 2007
Posts: 25
Location: Chennai,India

PostPosted: Wed Oct 24, 2007 12:13 pm    Post subject: Alter Table by adding a NOT NULL and UNIQUE KEY
Reply with quote

Thank You Friend,

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

Regards,
Shahul
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 24, 2007 12:44 pm    Post subject:
Reply with quote

Fine Shahul.

Issue the DB2 command
-DIS GROUP
Code:
DB2                                    DB2 SYSTEM    IRLM,
MEMBER   ID  SUBSYS CMDPREF   STATUS   LVL NAME      SUBSYS IRLMPROC,
-------- --- ----   --------  -------- --- --------  ----   --------,
........   0 DSN    -DSNDEV   ACTIVE   810 NANA      IRLM   DSNIRLM,
--------------------------------------------------------------------,


you will get the above information, in which you can find the version.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us