-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 17, 2001 11:30 AM
To: Steffen Goeldner
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: DBI: Foreign Key metadata
On Mon, Sep 17, 2001 at 09:34:51AM +0200, Steffen Goeldner wrote:
> 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?
I'd suggest ODBC with UNIQUE_OR_PRIMARY optionally tacked into the end.
(My general philosophy is that it ought to be possible to interchange
using DBD::Foo with DBD::ODBC (plus Foo's ODBC driver) with little or
no change to the application.)
Tim.
