CODEWITHSUNDEEP
sqlibm-midrangedb2-400
jwl
jwl

Reputation: 10514

Cannot add a Primary Key to AS400 table because of foreign key that doesn't exist

I have a table on an IBMi/System-i/iSeries/AS400 that has no foreign keys, primary keys or constraints of any kind. I am attempting to add a primary key using SQL:

ALTER TABLE MYLIB.TBLGB ADD CONSTRAINT MYLIB.R_MYLIB_PK_GB PRIMARY KEY (GBAPP, GBTSK, GBSET, GBSTP)

Which results in:

Error: [SQL0538] The FOREIGN key in constraint R_MYLIB_PK_GB in MYLIB not same as the parent key. Cause . . . . . : The FOREIGN key in constraint R_MYLIB_PK_GB in MYLIB is not the same as the parent key of table *N in *N. The FOREIGN key must have the same number of columns as the parent key and the data type and attributes of the FOREIGN key must be identical to the data type and attributes of the corresponding column of the parent key. Recovery . . . : Correct the statement so that the description of the FOREIGN key conforms to that of the parent key of the specified table.

I'm totally baffled by this because there is definitely no foreign key linked to this table. In fact, I have removed all constraints from all tables in this library. What can I look at to determine what is blocking this?

One additional interesting detail is that this has worked fine on other, essentially identical libraries...

Upvotes: 1

Views: 2704

Answers (3)

user2338816
user2338816

Reputation: 2163

Most likely, the sequence of save and restore was incorrect. The table had attributes that shouldn't have been saved and should have been removed before saving. The restore has resulted in a database catalog on the new system with inconsistent elements.

If not, then the next likelihood (and just about the only other possibility) is that the database catalog had inconsistencies already before the restore was done. Those could have been introduced by improper shutdowns or other actions.

First step should be:

RCLDBXREF OPTION(*CHECK)

If problems are reported, run:

RCLDBXREF OPTION(*FIX)

If the system is too old for the RCLDBXREF command, use:

RCLSTG SELECT(*DBXREF)

No "*CHECK" option is available for the older version.

Depending on size and complexity of your database, number of resynchronizations needed and general performance characteristics of your server, either of those can run from 10 minutes to a few hours. Most unexplained database catalog problems (other than those needing PTFs) can be cleared by either of those.

The RCLDBXREF command is usually preferred, but some problems will require the RCLSTG alternative. Significant restrictions exist for RCLSTG, so be sure to read the command [Help].

Upvotes: 0

Menno H.
Menno H.

Reputation: 1

Constraints are qualified by the schema. So unless you also have a schema called mylib, remove the qualification.

The reason that it is complaining about a table, is probably because it tries to find a table in the schema, systables, but that is pure conjecture.

Upvotes: 0

James Allman
James Allman

Reputation: 41198

Check the file description and database relations with the DSPFD and DSPDBR commands.

Upvotes: 0

Related Questions