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.
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 v22.214.171.124, on LinuxAMD6497