1

This db2 query:

alter table mylib.mytable add primary key(field1, field2, field3)

returns

[SQL0624] Table MYTABLE in MYLIB already has a primary key'.

However

alter table mylib.mytable drop primary key

returns:

[SQL0539] Table MYTABLEin MYLIB does not have a primary or unique key'.

How can I remove the existing primary key and create the new one?

The file was created in green screen, not by executing a SQL CREATE TABLE. The database version is V7R2.

  • Query the catalog views to verify what constraints, if any, exist for the table. – mustaccio Oct 20 '18 at 14:28
  • Sorry for the late response. If you mean SYSCAT and SYSSTAT, they are not available. I tried to add SYSCAT and SYSSTAT to "Schemas to display" in Navigator but it could not find them. – Test User Oct 29 '18 at 19:31
  • SYSCAT and SYSSTAT are the schemas used in DB2 for LUW. On IBM i you probably want to use the QSYS2 schema/library. – mustaccio Oct 29 '18 at 19:44
  • Sorry for the late response .This should have been the answer. The link is especially valuable. – Test User Feb 23 at 22:03
0

You can find all FKEY/PKEY/CHECK Constraints in QSYS2.TABLE_CONSTRAINTS to see and/or remove any keys built on the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.