Can we change the value of primary key in DB2

meetsrk · New User Joined: 13 Jun 2004 Posts: 85

hi ppl,

can we change the value of primary key in DB2?

if not, then you are in for a suprise
if yes, then tell me why is that happening?

i.e in a table of emp,
consider, empno - integer, not null and primary key
ename - char(20)
esal - decimal(7,2)

i can write a sql statment like,

update emp set empno = empno + 1

the above statment did execute succesfully and incremented all the values of empno by 1.
pls try and tell me.
bye,
meetsrk

priya · Posted: Sun Jun 27, 2004 9:51 pm

Yes, you can change the value of the primary key...

muthukumar · New User Joined: 24 Mar 2004 Posts: 29

Hi,

You can change the Primary key of the table but all the logs for that record will be lost if primary key is changed.

regards
Muthu

gilli · New User Joined: 28 Aug 2004 Posts: 5

hi meetsrk,

there is nothing to get surprised.
you had just changed the value of primary key and not the primary key itself.
bye,

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

hi meetsrk,
its an easy thing to read the primary key and to add update with the existing and then again update the primary key. i don't think there is nothing to surp. the thing is if you are updating the same key value then it will show the error.

krishnaiahc · New User Joined: 31 Aug 2005 Posts: 21

All,

I have one question , if we modify the primary key in parent table "what about the foreign of the that primary key" ..

Thanks,
Krishna

dewrid · New User Joined: 28 Oct 2005 Posts: 4

There are four options on a primary key update when there is a foreign key defined on it:
1. ON UPDATE RESTRICT
2. ON UPDATE NOACTION
3. ON UPDATE CASCADE
4. (DONT REMEMBER THE FOURTH ONE)

The first one wont allow you to update the key when there is a dependant key, the second also does the same9almost), while the third one modifies the dependant foreign key also with the new value of your parent key.
these options are declared when you are creating the child table.