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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Disclaimer: I do not know DB2.
I simply googled these with "db2 table definition".
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;
SELECT * FROM syscat.tabconst WHERE type = 'P';
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;