• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database tool to generate the list of parent and child tables through API (programmatically)

 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure whether this is the right forum to ask this question.

Is there a way to retrieve the list of parent and child tables for any given database (ex: oracle,MySql) programmatically (through API).Parent tables are basically the tables without any foreign key associated with it. Child tables are the one where the foreign key (primary key of parent table) is associated with it. Main objective is to avoid using queries and let API generate these list of Parent and child Tables. Is there any such tool(like Tableau,IBM Data Studio) to retrieve the list programmatically? I tried in both mentioned tool but it didn't work.

Could someone please help me at the earliest.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

One possibility is to use JDBC (see this for details). This certainly allows you to create a list programmatically. But given that you're looking for a "tool", it might be too low level for your purposes. Perhaps we might be able to give better advice if you tell us more details about what you need to accomplish.

There's a caveat in your definition of parent and child tables, though. Very often there are tables which both refer to other tables through a FK constraint and are referred by a different FK constraint themselves, so they're a parent table in respect to some tables and a child table in respect to some other tables. Your definition puts them only into the "child table" category, which sounds a bit problematic to me (but again, I don't know your goal).

Furthermore, using your definition, it is possible to have a schema without a parent table, for example if you have only two tables referencing each other. This isn't very common, but it can happen.

And lastly, a table can refer to itself. This is often used to model hierarchical entities. Again, you need to decide how to handle these tables.

(Edit: I've edited your post to stop a smiley being shown where it didn't belong.)
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin for the reply. I need to delete the data which is present in child table but not in parent table.
But first I need to show the parent tables as dropdown and based on the parent table selection the corresponding child tables should display.
Does JDBC support this functionality ? The link which you mentioned generates list of foreign keys only.
Main objective is to avoid using queries as I might deal with different databases.
Is there a method/way in JDBC that lists the parent table and its corresponding child tables.
Kindly also let me know if you know any database tool that support this functionality.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Praveen Sampath wrote:I need to delete the data which is present in child table but not in parent table.

Could you explain in more detail why this is needed at all?

The foreign key constraint should prevent having data in the child table which isn't present in the parent table in the first place. If you have a database into a state where this is not true, it can mean either a database bug (I've actually seen that one myself), or that someone intentionally or unintentionally allowed this situation to happen by using some more advanced database tools. We should try to find out what is happening and continue from there.
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. As you quoted , this situation might arise un-intentionally too while copying the data from one database to another database.
These things are done by the DBA.
My responsiblity is to help identify these data and remove it. There are various types of data which i am interested in removing it. This is one such type.
But this is secondary objective.
My primary objective is to first show the parent tables as dropdown and based on the user selection , I need to show the corresponding child tables
I am stuck in this primary objective as i have been looking for a tool to do this.
Can JDBC provide this functionality.
 
Paul Clapham
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wait a minute... you have a DBA but there isn't any documentation about the relationships between the tables in your database schema and you are supposed to write some code to figure it out? Something isn't right here.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin and Paul are right: It sounds like you have a problem when you are creating the data as you should not be able to create orphan records like this. If your DBA doesn't know how to maintain data integrity (which is one of the fundamental principles of the relational data model), then your company needs a new DBA. You (or your DBA) need to stop creating bad data, so make sure your FK constraints are properly defined and enabled where appropriate, and that you are trapping any errors during data load/migration etc.

Fix the bug, don't build a half-baked tool to maintain the results of the bug.

You can use SQL and your database's data dictionary to work out the relationships between tables where FK constraints have been defined, but of course this won't help you if no FK constraint exists in the first place. You can then use this knowledge to run SQL queries to find the orphan records by querying the relevant key columns on the parent and child tables. In Oracle, for example, you can use the MINUS operator to do a simple set comparison between the keys in each table:

SELECT DISTINCT child.keycol
FROM child
MINUS
SELECT parent.keycol
FROM parent

This will give you the list of keys that exist in the child but not in the parent.

As for visualising the data model, Oracle's free SQL Developer Data Modeler allows you to generate a schema diagram from the database. SQL Developer is a really useful free tool for Oracle-based development, so if you are developing applications on Oracle you should be using it.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with the previous posts as well: if you have defined proper foreign key (and other) constraints to maintain data integrity, it's impossible to insert orphan records in your database. And without any foreign key constraints it's impossible to determine the relations between different tables and you can't tell which of them are parent tables and which others are child tables, so your "database tool" would be completely unreliable (and very error-prone).
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks all for the solution. I am using JDBC now. I was successfully able to retrieve child table names using getExportedKeys. It is working fine in oracle but in mysql it is returning an empty set. I used useInformationSchema=true and used latest drivers too(5.0). I did google and saw people saying it works. Could someone please help me out.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
S Ganesh wrote:Could someone please help me out.

The topic about getExportedKeys returning an empty set can be found here.
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Roel De Nijs That is posted by me only. I wasn't sure whether to post a specific question in this forum itself or in a new forum. Could someone please reply to it and help me out.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All people in this discussion gave the advice to handle the root cause of this problem: make sure no orphan records are inserted using appropriate referential constraints. Why are you still trying to create an error-prone database tool?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic