Both, SQL/CLI and ODBC define a function 'SQLForeignKeys' for
obtaining foreign key metadata:
SQLRETURN SQLForeignKeys
(
SQLHSTMT StatementHandle,
SQLCHAR* PKCatalogName , SQLSMALLINT NameLength1,
SQLCHAR* PKSchemaName , SQLSMALLINT NameLength2,
SQLCHAR* PKTableName , SQLSMALLINT NameLength3,
SQLCHAR* FKCatalogName , SQLSMALLINT NameLength4,
SQLCHAR* FKSchemaName , SQLSMALLINT NameLength5,
SQLCHAR* FKTableName , SQLSMALLINT NameLength6
);
A perlish interface - similar to table_info and primary_key_info -
may look like:
$sth = $dbh->foreign_key_info( $PKCatalog, $PKSchema, $PKTable
, $FKCatalog, $FKSchema, $FKTable );
- $PK* identifies the primary key table.
- $FK* identifies the foreign key table.
- These parameters cannot contain string search patterns.
- If both $PK* and $FK* are given, the function returns the foreign key
(if any) in table $FK* that refers to the primary key of table $PK*.
(Note: In SQL/CLI, the result is implementation-defined.)
- If only $PK* is given, then the result set contains the primary key
of that table and all foreign keys that refer to it.
- If only $FK* is given, then the result set contains all foreign keys
in that table and the primary keys to which they refer.
(Note: In SQL/CLI, the result includes unique keys too.)
Because ODBC never includes unique keys, they define other columns
in the result set than SQL/CLI:
SQL/CLI ODBC
----------------- -------------
UK_TABLE_CAT PKTABLE_CAT
UK_TABLE_SCHEM PKTABLE_SCHEM
UK_TABLE_NAME PKTABLE_NAME
UK_COLUMN_NAME PKCOLUMN_NAME
FK_TABLE_CAT FKTABLE_CAT
FK_TABLE_SCHEM FKTABLE_SCHEM
FK_TABLE_NAME FKTABLE_NAME
FK_COLUMN_NAME FKCOLUMN_NAME
ORDINAL_POSITION KEY_SEQ
UPDATE_RULE UPDATE_RULE
DELETE_RULE DELETE_RULE
FK_NAME FK_NAME
UK_NAME PK_NAME
DEFERABILITY DEFERRABILITY
UNIQUE_OR_PRIMARY
The question is, which specification should DBI support?
Steffen
