On Wed, Sep 19, 2001 at 09:18:38AM +0200, Steffen Goeldner wrote:
> Tim Harsch wrote:
> >
> > 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...
>
> According to Date, the Relational Model no longer requires
> that one of the candidate keys is chosen as the primary key.
> However, in most cases, it will be a good idea.
>
> > 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.
>
> The problem is not (so much) the different column names or
> the additional column, but the different result sets.
> As Tim said:
>
> | 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.
>
> The Oracle ODBC driver, for instance, returns only FK's referencing
> PK's (I'll post some test cases later).
> Now imagine, a user relies on that behavior (and on the ODBC spec). He
> (she) will be surprised if we return additional FK's referencing
> alternate keys.
> On the other side, that additional information may be essential and
> I think about a way to provide it if the user requests it.
> Some thoughts:
>
> - We could provide two methods (ODBC vs. SQL/CLI).
Nope.
> - An additional method argument could switch the behavior.
> ODBC-mode (PK's only) is the default.
Nope.
> - A connection attribute (or similar) could switch the behavior.
> This is similar to 'SetEnvAttr' in ODBC.
Nope.
How about:
- Specify that if the 15th column is not null then it has
the same semantics as the SQL/CLI UNIQUE_OR_PRIMARY column.
> The deeper problem is (I cite from the SQL/CLI spec):
>
> | These special routines are only available for a small portion of
> | the metadata that is available in the Information Schema.
> | Other metadata ... must be obtained by issuing appropriate queries
> | on the views of the Information Schema.
>
> If all DBMS would provide a standard INFORMATION_SCHEMA ...
So true. But life's like that.
Tim.
