Re: Acc2 <-> Oracle 7.x: Primary key wanders...

Martin Schroeder wrote:
>
> Hi,
> we are using Access 2.0 on Win 3.1x as a client for an Oracle 7.x database.
> We recently noticed that Access and Oracle have different opions about
> which index is the primary key for an Oracle table.
> The setup in Oracle is correct---say a table has two keys A and B, both are
> unique, and A is the primary key---but Access thinks that another index is
> the primary key---B in this example. This problem persists even with a
> fresh database and newly attached table.

ODBC driver queries Oracle for unique indexes ordered by index name. By default, a PRIMARY KEY is implemented via a system generated index name beginning SYS. This default generated index name is probably sorting after your own UNIQUE index. Hence the UNIQUE index is used.

Two options:

Either rename your UNIQUE index name to sort after SYS... or
associate a named constraint with your primary key at creation time. The index for the primary key is named the same as the constraint. Therefore naming the constraint to sort before the your UNIQUE index will cause the primary key index to be used.

eg alter table con
Geoff Ingram

-- 
All my own personal opinions