srulison
asked on
How do I write a query that will display the columns designated as primary keys for each table in a Derby 10.11 database?
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,
CON.CONSTRAINTNAME, CON.SCHEMAID
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?
Thank you.
SELECT TA.TABLENAME, CO.COLUMNNAME, CO.COLUMNNUMBER, CO.REFERENCEID, CN.TABLEID,
CN.ISINDEX, CN.ISCONSTRAINT, KS.CONSTRAINTID, CON.CONSTRAINTID,
CON.CONSTRAINTNAME, CON.SCHEMAID
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?
Thank you.
ASKER
Well unfortunately the SQL client that I am using, Squirrel SQL returns unknown data type for the DESCRIPTOR field. The data type shown in the table description is org.apache.derby. catalog.IndexDescriptor.
As an FYI here is a link to all of the sys table layouts http://db.apache.org/derby/docs/10.3/ref/rrefsistabs38369.html.
Maybe it's just not feasible to get this information through a standard SQL statement. I know that it can be done in a java application using the getMetaData() method, (see below) and that led me to believe that it should be retrievable with a Select statement as well. The fact that I am getting an unknown data type on the DESCRIPTOR field is making me think twice about that.
DatabaseMetaData dm = conn.getMetaData( );
ResultSet rs = dm.getExportedKeys( "" , "" , "table1" );
while( rs.next( ) )
{
String pkey = rs.getString("PKCOLUMN_NAM E");
System.out.println("primar y key = " + pkey);
}
As an FYI here is a link to all of the sys table layouts http://db.apache.org/derby/docs/10.3/ref/rrefsistabs38369.html.
Maybe it's just not feasible to get this information through a standard SQL statement. I know that it can be done in a java application using the getMetaData() method, (see below) and that led me to believe that it should be retrievable with a Select statement as well. The fact that I am getting an unknown data type on the DESCRIPTOR field is making me think twice about that.
DatabaseMetaData dm = conn.getMetaData( );
ResultSet rs = dm.getExportedKeys( "" , "" , "table1" );
while( rs.next( ) )
{
String pkey = rs.getString("PKCOLUMN_NAM
System.out.println("primar
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Unfortunately it does not appear that there is a way to query the columns designated as a primary key in an Apache Derby database. That information appears to be contained in a field that is a hidden data type. The gentleman that responded to my question did all he could to help me with this issue but it appears that there just isn't a way to retrieve this data through a normal SQL select statement. I am therefore still giving him an A for providing the information that he did. At least now I know the field where this information resides.
To be a little more specific, I'm arguing this is not something that can be done outside of Java. Inside a Java application, with the proprietary class available, it should be possible to not only return the data properly, but use it natively for whatever operations are required.
ASKER
Sounds like the JAVA application is the only way to go. Thank you again for your help.
Open in new window
Now comes the fun part - that DESCRIPTOR is supposed to be a CSV list of column *numbers* in the table. You'll still need to pull the column list and isolate them somehow.