database - MS-Access Oracle Linked-Table Primary Key errors -

when linking oracle database ms access have been attempting find out key primary key (pk).

right-clicking , selecting design view on linked-table opens page explaining kind of data held within each field e.g., short text, includes key symbol next specific fields , says part of primary key.

i suspicious because first table did on apparently have composite primary key made of 5 columns, 2 of in-fact empty. rooted further around web , found this oracle page. seem looking @ all_cons_columns table can see actual pk's - , lo-and-behold seemingly can , there 1 pk in table given in previous example.

however, there seemingly strange occurrence within all_cons_columns table table in question lists pk constraint on 2 columns (note: not composite key, says constraint on column_x column_x pk , states rather randomly constraint on column_y column_x pk).

so, on why:

1. ms-access imports pk incorrectly.

2. why all_cons_columns table randomly adds in incorrect constraint on column?

i using all_constraints table , correct, is, includes 1 constraint, i.e., pk table_x column_x.

thanks in advance!

all_constraints doesn't show columns part of primary key constraint. constraint_type column tell particular constraint primary key constraint, you'll need @ all_cons_columns find out columns primary key composed of. note can't tell if constraint primary key it's name, since non primary unique constraint named table_pk though it's not primary key.

it's possible access taking first unique constraint it's finding on linked table , making primary key.

does query show your constraint single column or composite primary key?

select ac.owner      , ac.table_name      , ac.constraint_name      , case when max(nvl(position,0)) on (partition ac.owner, ac.table_name, ac.constraint_name) > 1 'composite '        end ||        case ac.constraint_type          when 'p' 'primary key'          when 'u' 'unique'          when 'r' 'foreign key'          when 'c' 'check'          else ac.constraint_type        end constraint_type      , acc.column_name      , acc.position   all_constraints ac   join all_cons_columns acc     on acc.owner = ac.owner    , acc.table_name = ac.table_name    , acc.constraint_name = ac.constraint_name  ac.owner = user  order ac.table_name      , ac.constraint_name      , acc.position;