Best practice: Test for existence of table etc.?

[Joachim Durchholz]

Hi all,

what's the best practice to test the existence of a
- table
- field in a table
- foreign key constraint?

Use case: Incremental database schema evolution.
I.e. rows in old and new format are in existence at the same time.
Which means columns from old and new format coexist; old-format columns
will get dropped once the last old-format row is gone.
(Yeah, I like doing crazy stuff :-D )

[Steve McLeod]

You can test with queries such as:

for tables

select count(*) from information_schema.tables where table_name = ?

this will return a one-row, one-column result set. if the table exists, it will return 1, otherwise it will return 0

for columns:

select count(*) from information_schema.columns where table_name = ? and column_name = ?

I don't know the solution for foreign key constraints off the top of my head, but the solution can be found in one of the tables in information_schema

[Thotheolh]

Here's an interesting you can try with the H2 database web console. Launch into your web console and login to your database. Click on the "INFORMATION_SCHEMA" tree and click the tables in that tree to navigate your tables' metadata and information.

Clicking around the "INFORMATION_SCHEMA" tree would also generate SQL statements for you to use.

If you are looking for "REFERENTIAL" type constraints which effective means foreign key constraints, you can click on the "CONSTRAINTS" node inside the "INFORMATION_SCHEMA" tree. 

Essentially the automated web console would generate the following statement " SELECT * FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' " if you are looking for foreign key indexes.

Please try your hands on the wonderful web console which Thomas Mueller have made. It is simply amazing how a tiny jar file database engine could package in by default a beautiful and amazingly powerful web console which makes life so much easier for users.

H2 database is truely an impressive work.