Finding Foreign Keys From Unknown Database SQL and Java

Question

I have a java program that queries through any type of database with any amount of tables. The user can put in a string and it will return the table and rows containing that string. The problem is that some of these tables have foreign keys. How could I recursively go through all of tables finding foreign keys and rows without any knowledge before hand about the Database?

Plateform: Windows 7(64) Database Type : Postgres

Code:

 public static void connectPostGres(String type, String server, String database, String port, String username, String password) {
        System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? Include in your library path!");
            e.printStackTrace();
            return;
        }
        System.out.println("PostgreSQL JDBC Driver Registered!");
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://" + server + ":" + port + "/" + database, username, password);
            //connection = DriverManager.getConnection("*********);
        } catch (SQLException e) {
            System.out.println("doh!");
            e.printStackTrace();
            return;
        }
        if (connection != null) {
            System.out.println("Searching...");
            LinkedList allTables = new LinkedList();
            try {
                Statement st = connection.createStatement();
                ResultSet rs = st.executeQuery("select * from pg_tables");//get all the tables
                while (rs.next()) {
                    String myString = rs.getString("tablename");
                    if (myString.trim().charAt(0) == 'p' && myString.trim().charAt(1) == 'g') {
                        //this is just some crappy tables we dont want to query through
                    } else if (myString.trim().charAt(0) == 's' && myString.trim().charAt(1) == 'q' && myString.trim().charAt(2) == 'l') {
                        //this is just some crappy tables we dont want to query through
                    } else {
                        allTables.add(myString);//add all tables to a linkedlist
                    }
                }

                rs.close();
                String masterQuery = "";
                for (int i = 0; i  columnList = new LinkedList();
                    for (int j = 1; j  0) {
                        for (int j = 0; j

Unreason Unreason · Accepted Answer · 2010-10-04T15:55:26

You can read this informative article about retrieving postgres meta data.

Typically you have to dig through INFORMATION SCHEMA or use system catalog.

EDIT
Also, put as much of your logic into SQL to avoid retrieving and processing the data you don't need at all.

Here's a copy/paste from mentioned article, that lists all constraints (you'll find both approaches here)

SELECT c.conname AS constraint_name,
          CASE c.contype
            WHEN 'c' THEN 'CHECK'
            WHEN 'f' THEN 'FOREIGN KEY'
            WHEN 'p' THEN 'PRIMARY KEY'
            WHEN 'u' THEN 'UNIQUE'
          END AS "constraint_type",
          CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,
          CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,
          t.relname AS table_name,
          array_to_string(c.conkey, ' ') AS constraint_key,
          CASE confupdtype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_update,
          CASE confdeltype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_delete,
          CASE confmatchtype
            WHEN 'u' THEN 'UNSPECIFIED'
            WHEN 'f' THEN 'FULL'
            WHEN 'p' THEN 'PARTIAL'
          END AS match_type,
          t2.relname AS references_table,
          array_to_string(c.confkey, ' ') AS fk_constraint_key
     FROM pg_constraint c
LEFT JOIN pg_class t  ON c.conrelid  = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
    WHERE t.relname = 'testconstraints2'
     AND c.conname = 'testconstraints_id_fk';

-- with INFORMATION_SCHEMA:

   SELECT tc.constraint_name,
          tc.constraint_type,
          tc.table_name,
          kcu.column_name,
      tc.is_deferrable,
          tc.initially_deferred,
          rc.match_option AS match_type,
          rc.update_rule AS on_update,
          rc.delete_rule AS on_delete,
          ccu.table_name AS references_table,
          ccu.column_name AS references_field
     FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
       ON tc.constraint_catalog = kcu.constraint_catalog
      AND tc.constraint_schema = kcu.constraint_schema
      AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
       ON tc.constraint_catalog = rc.constraint_catalog
      AND tc.constraint_schema = rc.constraint_schema
      AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
       ON rc.unique_constraint_catalog = ccu.constraint_catalog
      AND rc.unique_constraint_schema = ccu.constraint_schema
      AND rc.unique_constraint_name = ccu.constraint_name
    WHERE tc.table_name = 'testconstraints2'
      AND tc.constraint_name = 'testconstraints_id_fk';

Finding Foreign Keys From Unknown Database SQL and Java

2 Answers