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).
- An additional method argument could switch the behavior.
ODBC-mode (PK's only) is the default.
- A connection attribute (or similar) could switch the behavior.
This is similar to 'SetEnvAttr' in ODBC.
Steffen
P.S.:
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 ...
