My HSQLDB schema inspection story

This is a simple story of my need to inspect the schema of an HSQLDB database for a participar FOREIGN KEY, and the interesting things I had to do to actually inspect it.

I am using an HSQLDB 1.8 database in one of my web applications. The application has been developed using the Play framework, which by default uses JPA and Hibernate.

A few days back, I wanted to inspect the schema which Hibernate had created for one of my model objects. I started the HSQLDB database on my local machine, and then started the database manager with the following command

java -cp ./hsqldb-1.8.0.7.jar org.hsqldb.util.DatabaseManagerSwing

When I tried the view the schema of my table, it showed me the columns and column types on that table, but it did not show me columns were FOREIGN KEYs.

Image 1: Table schema as shown by HSQLDB's database manager

I decided to search on StackOverflow and find out how I could view the full schema of the table in question. I got a few hints, and they all pointed to the system tables, so I decided to turn on the "show system tables" option from HSQLDB's view menu.

The first table that caught my eye was information_schema.system_tables, so I fired the query:

select * from information_schema.system_tables;

This gave me all the system as well as application tables, but did not give me the detail I was looking for, which was the FOREIGN KEY contstraints on the 'USERREGISTRATIONDATE' table.

The next table that caught my eye was information_schema.system_table_constraints, so I fired the following query:

select * from information_schema.system_table_constraints where table_name = 'USERREGISTRATIONDATE';

This helped a bit further. It told me that the USERREGISTRATIONDATE table had a FOREIGN KEY and the constraint name was 'FK98DCB61247140EFE'.

Image 2: Result from table INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS

Nice, but it still did not tell me which column the constraint refered to.

The next table I came across was information_schema.system_crossreference. This seemed to have a column called 'FK_NAME'. Good, I fired the query:

select * from information_schema.system_crossreference where FK_NAME='FK98DCB61247140EFE';

Here is what this table showed me...

Image 3: Result from table INFORMATION_SCHEMA.SYSTEM_CROSSREFEREBCE

Awesome, this time it showed me the name of the the FOREIGN KEY 'FK98DCB61247140EFE' references the 'ID' column of table 'USER'

I finally had my answer, but before ending this post, let me mention just one more thing. I realized that I did not need the information_schema.system_table_constraints table at all. I only needed the information_schema.crossreference table with the following query:

select PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_NAME, FKCOLUMN_NAME 
from information_schema.system_crossreference 
where FKTABLE_NAME='USERREGISTRATIONDATE' and FKCOLUMN_NAME='USER_ID';

Image 4: Finally the query that clearly showed me the FOREIGN KEY

Now all this should not have been so time consuimng. The GUI tool should have given me this information right away.