Is there a generic way, using ODBC/OLEDB/ADO.NET or another
programmable database technology to determine foreign and primary key
constraints in a table?

I've tried using the ODBC API's SQLPrimaryKeys and SQLForeignKeys but
they aren't always supported. I've also tried SQLSpecialColumns and
SQLStatistics for limited success with Primary Keys, but no idea for
Foreign Keys.

Thanks!

On 08.05.2007 19:28 Jason D. wrote:
The JDBC drivers do reveal this information as well (that would fall in
the category "another programmable database technology")

With JDBC this is up to the driver to support the calls or not (they are
not required, as far as I know). I could image this is similar with the
ODBC specs (I haven't used any of the mentioned interfaces)

Look at the DatabaseMetaData class in the JDK documentation.

Thomas

On May 9, 3:47 am, Thomas Kellerer <JUAXQOSZF... (AT) spammotel (DOT) com> wrote:
Thanks for the advice! It's a pain trying to use the ODBC API for
it.....the calls are Level 2 and don't seem to be well supported. I'm
having good luck with OLEDB using the OleDbSchemaGuid Class in .NET;
though I might try doing it with .NET.

Cheers,
Jason

"Jason D." <Tronman (AT) gmail (DOT) com> wrote

I don't know the specific answers to your question, but in general.....


If the foreign key/primary key pair have been declared in a referential
integrity constraint, and if the constraint has been catalogued in the
metadata (system tables), and if the metadata conforms to a database
industry standard (like the SQL metadata standard) reasonably well,
then you should be able to compose your own query to detect the foreign
key/primary key pairs.

Alternatively, if the DBMS supports user defined domains, and if the
domains were defined somewhat intelligently, and if this is catalogued in
the metadata, it should be possible to pull a query that would group all
collections of more than one column that are based on a single domain. The
query should be pretty easy. This won't limit you to foreign key/primary
key pairs, but it should provide the information you need, mixed in with a
little possibly extraneous information.

I realize that this is less helpful than pointing you at a tool, but I hope
someone reading can make some use of it.

Take a look at http://www.devx.com/Java/Article/32443 for a simple to
use, and open source, Java API to obtain all types of database
metadata.

Sualeh Fatehi.