How do I write a query that will display the columns designated as primary keys for each table in a Derby 10.11 database?
Posted on 2015-01-12
I am trying to find a way to write a select statement that will show all of the primary keys for each table in a database. The describe table utility provides some useful information but it does not, as far as I can tell, give any info pertaining to either primary or foreign keys. The closest I have come to getting the results I am looking for is this select statement:
SELECT TA.TABLENAME, CO.COLUMNNAME, CO.COLUMNNUMBER, CO.REFERENCEID, CN.TABLEID,
CN.ISINDEX, CN.ISCONSTRAINT, KS.CONSTRAINTID, CON.CONSTRAINTID,
FROM SYS.SYSTABLES TA, SYS.SYSCOLUMNS CO, SYS.SYSCONGLOMERATES CN, SYS.SYSKEYS KS, SYS.SYSCONSTRAINTS CON
WHERE TA.TABLEID = CO.REFERENCEID AND
CN.TABLEID = TA.TABLEID AND
CN.CONGLOMERATEID = KS.CONGLOMERATEID AND
KS.CONSTRAINTID = CON.CONSTRAINTID
The SYSKEYS appears to be the table that contains data relating to the primary keys for each table in a database. That in conjunction with the CONGLOMERATES, SYSCONSTRAINTS, SYSCOLUMNS and SYSTABLES would seem to be enough to get the output I am looking for but there are no fields in the SYSCOLUMNS table that will link either directly or indirectly to a record in the SYSKEYS table. The report produced from the query shown above lists the all of the columns in a table that relates to a record in the SYSKEYS table. In other words I am only able to find the table that contains the primary key referenced in a row in the SYSKEYS table. I am not able to drill down any further than that.
Does anybody have an idea what need to do to get this narrowed down to the actual column in a table that is the primary key for that table?