Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Adding a PRIMARY KEY

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

New User


Joined: 26 Jan 2006
Posts: 23
Location: London

PostPosted: Wed Jul 09, 2008 3:33 pm    Post subject: Adding a PRIMARY KEY
Reply with quote

Hi,

Suppose I have a table SAMPLE with a nullable column SNO CHAR(5). I have inserted a couple of records to the table (but no null values are inserted). Now if I have to make SNO the primary key of the table, I have to make the column not nullable. Could you please share the corresponding ALTER TABLE ALTER COLUMN syntax?

Many Thanks,
Simi
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Wed Jul 09, 2008 3:41 pm    Post subject:
Reply with quote

Simi,

You can use the following ddl to create primary key for the table -

Code:
ALTER TABLE <table name>
PRIMARY KEY (col1,col2.....);


If a primary key is already existing for the table, you need to drop it before you can execute the above ddl -

Code:
ALTER TABLE <table name> DROP PRIMARY KEY
Back to top
View user's profile Send private message
simimathew

New User


Joined: 26 Jan 2006
Posts: 23
Location: London

PostPosted: Wed Jul 09, 2008 3:46 pm    Post subject: Reply to: Adding a PRIMARY KEY
Reply with quote

Hi Murali,

Thanks for the quick reply.

But I was asking for the syntax to make an already existing nullable column to not nullable. My problem is I have a nullable column (though the values are all NOT NULL) and I would like to make that column my primary key. When i try adding the primary key, it gives me an error saying that the column can contain null values.

Thanks again,
Simi
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 09, 2008 4:27 pm    Post subject:
Reply with quote

If you see the ALTER stmt syntax there is no provision for changing a column from NULL to NOT NULL ... at the max wht u will be able to do is add a check constraint on the col for NOT NULL ..but in this case you cannot make this col as your primary key ...

I am not sure whether any other possibilities are there
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 09, 2008 4:42 pm    Post subject:
Reply with quote

from IBM site ...

The ALTER TABLE statements that you cannot use are those that require scanning of column data to verify the validity of the alteration operations. Specifically, this means that you cannot execute the following statements on a table:

ADD UNIQUE CONSTRAINT
ADD CHECK CONSTRAINT
ADD REFERENTIAL CONSTRAINT
ALTER COLUMN SET NOT NULL
ALTER TABLE ADD REFERENTIAL CONSTRAINT
ALTER TABLE ADD CONSTRAINT
ALTER TABLE ADD UNIQUE CONSTRAINT
Back to top
View user's profile Send private message
simimathew

New User


Joined: 26 Jan 2006
Posts: 23
Location: London

PostPosted: Wed Jul 09, 2008 5:52 pm    Post subject: Reply to: Adding a PRIMARY KEY
Reply with quote

Thanks very much Ashimer
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 Reformatting and adding additional fi... Badbeef SYNCSORT 6 Thu Apr 05, 2018 4:45 pm
No new posts Receommendations required for adding ... Alan Playford JCL & VSAM 4 Wed Feb 14, 2018 4:01 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us