If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Primary Key from "generated by default" to "generated always"

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 10
Primary Key from "generated by default" to "generated always"

Hello,
I am trying to move a database from MySQL to DB2 and I am confrunting with the following problem (I will describe it as simple as possible):
I created a table TEST with the columns (ID, SUBJECT_ID)
In order to keep all the IDs from the old (mysql) table, I created the DB2 table like this:
CREATE TABLE TEST (
ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,
SUBJECT_ID INTEGER NOT NULL
CONSTRAINT PK_TEST PRIMARY KEY ( ID) ,
CONSTRAINT FK_TEST_SUBJECT FOREIGN KEY (SUBJECT_ID) REFERENCES SUBJECT (ID) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION )

Then I inserted all data from the old table:
INSERT INTO TEST (ID, SUBJECT_ID) VALUES
(1, 2), (2, 8), ...... (18133, 4259)

Now I need the ID to be generated always when inserting new values for SUBJECT_ID.
So I altered the ID column:
ALTER TABLE TEST ALTER COLUMN ID SET GENERATED ALWAYS
but when I am trying to insert a new row I get this error:

SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "TEST" from having duplicate values for the index key.
SQLSTATE=23505

I must mention that the old ID column does not contain all the values from 1 to 18133. There are some big gaps in this interval.
And another important thing: if I insert just few lines (up to 9 or 10 I think) from the old table, then, on a new insert (INSERT INTO TEST (subject_id) VALUES (9040)), the new ID is automatically generated.

My DB2 version is v9.7.0.0, on LinuxAMD6497

Thank you!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
You need to set the identity column to the correct value, which is now 1 since you created the table with "START WITH 1".

alter TEST alter ID restart with xxx
(where xxx is the highest value already used plus 1)

You should upgrade to fixpack 8, since you appear to using fixpack 0. There are about 400 bugs fixed in every fixpack, so in your case that is about 3200 bugs in your version of DB2 that have been fixed in FP 8. Fixpacks are cumulative, so you only need to upgrade to the latest.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 43
Try the following:

ALTER TABLE YOUR_TABLE_NAME DROP PRIMARY KEY!

ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)!

ALTER TABLE YOUR_TABLE_NAME ADD CONSTRAINT PK PRIMARY KEY (ID)!

SELECT MAX(ID)+1 FROM YOUR_TABLE_NAME!

ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID RESTART WITH "MAX(ID)+1" (replace with value from last sentence"!


HTH
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 10
Thank you both very much!
It works without dropping and creating the primary key.
You saved me a lot of time.
Have a nice rest of day!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
Quote:
Originally Posted by CCMF View Post
Try the following:

ALTER TABLE YOUR_TABLE_NAME DROP PRIMARY KEY!

ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)!

ALTER TABLE YOUR_TABLE_NAME ADD CONSTRAINT PK PRIMARY KEY (ID)!

SELECT MAX(ID)+1 FROM YOUR_TABLE_NAME!

ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN ID RESTART WITH "MAX(ID)+1" (replace with value from last sentence"!


HTH
If one had to drop the PK and recreate it just to reset the identity column START number, identity columns would be useless.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On