Easy to identify: Does the table have a primary key?

Primary Key

Next week I am going to teach students database basics again. One of the topics will be primary keys and how they help enforcing uniqueness and identify each of the stored objects. Recently I stumbled over the question how it is easily possible to tell whether a DB2 or dashDB table has a primary key. The answer, as often, is in the catalog, the database metadata.

For performance reasons almost all database systems use an unique index to implement a primary key. So the key (pun intended) is to look for such an index. Both DB2 for Linux, UNIX, and Windows (LUW) and DB2 for z/OS store information about indexes in a system table SYSIBM.SYSINDEXES. On DB2 for z/OS that table is exposed to the user and documented here. DB2 LUW has catalog views on top and the view to use is named SYSCAT.INDEXES, however querying the table still works:



SELECT COLNAMES
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'MYTABLE'
      AND UNIQUERULE = 'P'

The query returns the columns on which the primary key is defined for the table MYTABLE. As can be seen in the documentation, the UNIQUERULE provides information about whether the index is an index with duplicates, an unique index, or it is used to implement a primary key (value P). On DB2 LUW we could write the query utilizing the catalog view SYSCAT.INDEXES. The following query returns the table name and schema as well as the column names for all tables which have a primary key defined:


SELECT TABNAME, TABSCHEMA,COLNAMES
FROM SYSCAT.INDEXES
WHERE UNIQUERULE='P'

So the key to quickly working with primary keys are indexes and their metadata...