Forgive me for listening in as I don't actually design any DBD modules. But
from a user standpoint, I am probably one of the few individuals you will
find that actually has both a PK and a UNIQUE constraint on a table. With
some child tables referencing the PK and some referencing the Unique. This
is do to a legacy problem with the schema which I hope to fix so that all
children reference the PK. I know this kind of relationship is a bad thing
( for instance it confuses the heck out of ER Studio). However, it is
achievable in at least Oracle and Sybase. Which brings me to my point...
Why not support both types?
SQL/CLI ODBC
----------------- -------------
UK_TABLE_CAT PKTABLE_CAT
UK_TABLE_SCHEM PKTABLE_SCHEM
UK_TABLE_NAME PKTABLE_NAME
UK_COLUMN_NAME PKCOLUMN_NAME
DBDs with no concept of Unique constraints (or PKs for that matter) could
just return -1 or something.
> -----Original Message-----
> From: Kerspern, Nicolas [mailto:[EMAIL PROTECTED]]
> Sent: Monday, September 17, 2001 2:32 AM
> To: [EMAIL PROTECTED]
> Subject: RE: DBI: Foreign Key metadata
>
>
>
>
> -----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.
>
