从未知数据库SQL和Java中查找外键

2 回答

• 1

  您可以在DatabaseMetaData中查询此类信息：

  public static void main(String[] args) throws Exception {
      Connection connection = null;
      try {
          Class.forName(DRIVER);
          connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
  
          DatabaseMetaData metadata = connection.getMetaData();
          ResultSet resultSet = metadata
              .getExportedKeys(null, null, TABLE);
          while (resultSet.next()) {
          String pkTableName = resultSet.getString("PKTABLE_NAME");
          String pkColName = resultSet.getString("PKCOLUMN_NAME");
          String fkTableName = resultSet.getString("FKTABLE_NAME");
          String fkColName = resultSet.getString("FKCOLUMN_NAME");
          }
      } catch (SQLException e) {
          e.printStackTrace();
      } finally {
          connection.close();
      }
      }
  

  见DatabaseMetaData

  回复于 2024-04-26T07:54:40+08:00
• 1

  您可以阅读有关检索postgres元数据的信息article .

  通常，您必须深入了解INFORMATION SCHEMA或使用系统目录 .

  EDIT
  此外，将尽可能多的逻辑放入SQL中，以避免检索和处理根本不需要的数据 .

  这是来自上述文章的复制/粘贴，其中列出了所有约束（您可以在这里找到这两种方法）

  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';
  

  回复于 2024-04-26T07:54:40+08:00