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
 

 

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 Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Adding a descending character to a file Steve Ironmonger DFSORT/ICETOOL 3 Fri Jul 15, 2016 4:57 pm
No new posts SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm
No new posts SE37 while adding element to endevor kalyan.v CA Products 5 Mon Apr 25, 2016 6:05 pm


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