Solved

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
7
376 Views
Last Modified: 2015-01-16
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.
0
Comment
Question by:srulison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40546812
So, first I look at SYSCONSTRAINTS.  You'll need to check TABLEID and TYPE to isolate the PK you want to describe, and get the CONSTRAINTID.  Match that to SYSKEYS to get the CONGLOMERATEID, which you can then follow back to SYSCONGLOMERATES.DESCRIPTOR.  So:
SELECT c.DESCRIPTOR FROM
  SYSCONSTRAINTS a 
    INNER JOIN SYSKEYS b ON a.CONSTRAINTID=b.CONSTRAINTID
    INNER JOIN SYSCONGLOMERATES c ON b.CONGLOMERATEID = c.CONGLOMERATEID
  WHERE a.TABLEID='my_table_name' AND a.TYPE='P'

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.
0
 

Author Comment

by:srulison
ID: 40547399
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_NAME");
  System.out.println("primary key = " + pkey);
}
0
 

Author Comment

by:srulison
ID: 40547427
Here are two images of select statements that I ran after reading your post.

Select-1.jpg
Select2.jpg
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 40549101
That's the same problem I ran into with my previous research.  The DESCRIPTOR field is not a SQL-92 standard data type - it is a data type specific to derby.  Even worse, a quote from the table definition page, for the column's type:
org.apache.derby.
catalog.IndexDescriptor:

This class is not part of the public API.
So, it's not something meant to be used externally, either.  I did find the interface page describing the DESCRIPTOR behind-the-scenes class, but it merely shows what is possible to access using Java libraries.  As a final hint, you can always download the SVN source for derby to find the implementation yourself.  I think that is the route you'll have to go.
0
 

Author Closing Comment

by:srulison
ID: 40550521
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.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40551448
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.
0
 

Author Comment

by:srulison
ID: 40553982
Sounds like the JAVA application is the only way to go.  Thank you again for your help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
maven disable workspace resolution 1 41
collection output issue 9 66
What browser will run Java? 7 127
Dynamic Table mySQL stored procedure 5 37
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question