Adding one primary and one foreign key adds total four keys

Question

Hi All,

I am trying to alter table to add one primary key and one foreign key in a table. However when I am running the alter table query, I always get four values, two primary keys and two foreign keys. Below is the query I am running:

alter table INFO add constraint pk primary key(ID_NUMBER) add constraint fk foreign key(REV_NUMBER) references REVIEW_INFO

which gives the resultset as below:

CONSTNAME TYPE COLNAME

---

FK F REV_NUMBER
PK P REV_NUMBER
FK F ID_NUMBER
PK P ID_NUMBER

I am running SYSCAT.TABCONST to find the constraint details.

Please help me out to find out the problem where query is going wrong.

Answer 1

Are you sure you're not accidentally running the query twice?

Answer 2

Your ALTER TABLE statement adds 2 rows to SYSCAT.TABCONST. The other two constraints exist on other table(s) in your database, and would have been there before the ALTER TABLE statement was executed. If you include the TABNAME column from SYSCAT.TABCONST you could identify which table each of these constraints applies to.

We can deduce some information based on your results:

CONSTNAME TYPE COLNAME
--------- ---- -------------
FK        F    REV_NUMBER      <-- Created by your ALTER TABLE statement
PK        P    REV_NUMBER      <-- Pre-existing primary key.  Probably on REVIEW_INFO table 
FK        F    ID_NUMBER       <-- Pre-existing foreign key, against unknown table.
PK        P    ID_NUMBER       <-- Created by your ALTER TABLE statement

Note:

Something is strange here -- the view SYSCAT.TABCONST implies that you're running on DB2 for LUW, yet SYSCAT.TABCONST does not have (and has never had) a column called COLNAME. However, since you don't supply the query you executed to produce the results, I'll assume that is joining SYSCAT.KEYCOLUSE to get the column name.