Thread: find foreign key name

Hello.

I want to programatically drop a foreign key constraint via jdbc. How
can I find the foreign key name for a PostgreSQL foreign key via jdbc?
I know all about the related tables and columns, but not the name of
the constraint.

Regards,
  - Tore.

Tore Halset wrote:
> I want to programatically drop a foreign key constraint via jdbc. How
> can I find the foreign key name for a PostgreSQL foreign key via jdbc? I
> know all about the related tables and columns, but not the name of the
> constraint.

You can query the pg_constraint catalog table:

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html

There seems to be some example queries in the user comments that you
might be interested.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

On Dec 18, 2007, at 10:36 , Heikki Linnakangas wrote:

> Tore Halset wrote:
>> I want to programatically drop a foreign key constraint via jdbc.
>> How can I find the foreign key name for a PostgreSQL foreign key
>> via jdbc? I know all about the related tables and columns, but not
>> the name of the constraint.
>
> You can query the pg_constraint catalog table:
>
> http://www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html
>
> There seems to be some example queries in the user comments that you
> might be interested.

Thanks. I was hoping for something easy in
DatabaseMetaData#getExportedKeys.. :) But looking at pg_constraint
should work fine.

Regards,
  - Tore.

On Tue, 18 Dec 2007, Tore Halset wrote:

> Thanks. I was hoping for something easy in DatabaseMetaData#getExportedKeys..
>

What about the FK_NAME column in that result, doesn't that work?

Kris Jurka

Hi Tore,
As Kris indicated perhaps FK_NAME should do it. I have
used something a little different Im not sure why unless
I looked a little closer, but this what I have

MyJSQLView Project
http://sourceforge.net/projects/myjsqlview/
Code | Browse | --- src/Panels/net/danap/myjsqlview
Class TableTabPanel_PostgreSQL.java Class Method
getColumnNames() ....

// Make a final check to see if there are any keys columns
// columns in the table. If not then try foreign keys.

if (primaryKeys.isEmpty())
{
  rs = dbMetaData.getImportedKeys(tableMetaData.getCatalogName(1),
     tableMetaData.getSchemaName(1),
                  tableMetaData.getTableName(1));

  while (rs.next())
  {
     if (columnNamesHashMap.containsValue(rs.getString("PKCOLUMN_NAME")) &&
     !primaryKeys.contains(rs.getString("PKCOLUMN_NAME")))
     {
    primaryKeys.add(rs.getString("PKCOLUMN_NAME"));
    //System.out.println(rs.getString("PKCOLUMN_NAME"));
     }
   }
}

I was reluctant to access system catalogs directly in order to
derive the info. The above or FK_NAME would be a better way
to isolate from future changes in system catlogs.

dana

> On Dec 18, 2007, at 10:36 , Heikki Linnakangas wrote:
>
>> Tore Halset wrote:
>>> I want to programatically drop a foreign key constraint via jdbc.
>>> How can I find the foreign key name for a PostgreSQL foreign key
>>> via jdbc? I know all about the related tables and columns, but not
>>> the name of the constraint.
>>
>> You can query the pg_constraint catalog table:
>>
>> http://www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html
>>
>> There seems to be some example queries in the user comments that you
>> might be interested.
>
> Thanks. I was hoping for something easy in
> DatabaseMetaData#getExportedKeys.. :) But looking at pg_constraint
> should work fine.
>
> Regards,
>   - Tore.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On Dec 18, 2007, at 17:05 , Kris Jurka wrote:

> On Tue, 18 Dec 2007, Tore Halset wrote:
>
>> Thanks. I was hoping for something easy in
>> DatabaseMetaData#getExportedKeys..
>
> What about the FK_NAME column in that result, doesn't that work?

Thanks a lot. It works perfect. Should have seen that myself...

Regards,
  - Tore.