How can you tell if a table in DB2 has a Primary Key?

Question

We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined.

Is there a way using a select statement on the DB to see if a given table has one?

Thanks.

Answer 1

Disclaimer: I do not know DB2.

I simply googled these with "db2 table definition".

Source:

SELECT * 
FROM SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL 
WHERE TAB.CREATOR = COL.TBCREATOR 
AND TAB.CREATOR = 'xxxx' 
AND TAB.NAME = 'xxxxxxxxxxxxx' 
AND TAB.NAME = COL.TBNAME 
AND TAB.TYPE = 'V' ( OR 'T' ) 
ORDER BY 1,2;

Source:

SELECT * FROM syscat.tabconst WHERE type = 'P';

Answer 2

you can do a db2look, which will give you the ddls for the table.

db2look -d db_name -e -x -z schema_name -t table_name

Answer 3

This is probably the easiest option, since a primary key is supported by a matching index:

select COLNAMES from SYSIBM.SYSINDEXES where tbname = 'TABLE' and uniquerule = 'P';

You can also query the columns catalog table:

select NAME from SYSIBM.SYSCOLUMNS where tbname = 'TABLE' and keyseq > 0 order by keyseq;

Answer 4

1. SYSCAT.INDEXES.UNIQUERULE ( If UNIQUERULE is P then primary Key )
2. SYSCAT.TABCONST.TYPE ( if Type is P then Primary Key Constraint )