Database : SQL to find reference tables


To find all tables that referenced “Organization” table use the following SQL query:

select table_name, constraint_name, status from user_constraints
where constraint_type = ‘R’
and r_constraint_name in
select constraint_name from user_constraints
where constraint_type in (‘P’, ‘U’)
and table_name = ‘ORGANIZATION’
order by table_name, constraint_name;

NOTE: In the query above ‘ORGANIZATION’ can be replaced with the name of any table you are interested in.


Hibernate : how to create database tables automatically

When you are dealing with the database as a java programmer, you need not to have data table creation scripts for various databases. Hibernate creates the database tables for you automatically based on the entity relations ships and associations if you configure that way.

The most simple way to configure it is below tag in hibernate.cfg.xml
<property name=””>create</property>

So the list of possible options are,

  • create-drop: executes SchemaExport when SessionFactory initializes and drops the schema at the end of the life of the factory.
  • create: creates the schema, destroying previous data.
  • update: update the schema according to the changes made in the hibernate entities. eg if some columns/mappings/associations are added or droped.
  • validate: validate the schema, makes no changes to the database. if the schema is not up to date to run hibernate program, it will through an error

NOTE : Despite folks in Hib do their best, you simply cannot rely on automatic updates in production. Write you own patches, review them with DBA, test them, then apply them manually.