HiveBrain v1.2.0
Get Started
← Back to all entries
snippetModerate

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canprimaryyoudb2tellhashowtablekey

Problem

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.

Solution

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';

Code Snippets

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';

Context

StackExchange Database Administrators Q#2824, answer score: 11

Revisions (0)

No revisions yet.