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-184.108.40.206.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.
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'.
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...
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
where FKTABLE_NAME='USERREGISTRATIONDATE' and FKCOLUMN_NAME='USER_ID';
Now all this should not have been so time consuimng. The GUI tool should have given me this information right away.