All the testing was done by myself on Windows 10 64 bit using QGIS 2.18.3 against Oracle 12.1.
The requests below come from consulting work that I have completed for a customer who is using QGIS Desktop and QGIS Server against Oracle Spatial and are affecting their implementation.

Issue 1:
The current QGIS Oracle code that discovers the column_name associated with a primary key of a table (or materialized view) does not support situations where the constraint_name is not equal to the index_name.¶

create table foo (
fid number(10,0),
fid_int Integer,
fid_num9 number(9,0),
fid_num8 number(8,0),
fid_num7 number(7,0),
an_attribute varchar2(10),
geom sdo_geometry
);

ALTER TABLE FOO ADD CONSTRAINT FOO_PK PRIMARY KEY (FID);

Current QGIS SQL for detecting PK:

SELECT column_name
FROM all_ind_columns a
JOIN all_constraints b
ON a.index_name=constraint_name AND a.index_owner=b.owner
WHERE b.constraint_type='P' AND b.owner='QGIS' AND b.table_name='FOO';
-- FID

However, an alternate and very commonly used method for creating a primary key is:

alter table foo drop constraint foo_pk;
create unique index foo_fid_uidx on foo(fid); /* Create UNIQUE Index Before define primary key /
alter table foo add constraint foo_pk primary key (fid) using index foo_fid_uidx; / Note that PK uses pre-existing unique index */

The current QGIS SQL can be easily modified to cope with this situation:

SELECT column_name
FROM all_ind_columns a
INNER JOIN
all_constraints b
ON ( a.index_owner=b.owner AND a.table_name=b.table_name AND a.index_name=b.index_name )
WHERE b.constraint_type='P' AND b.owner='QGIS' AND b.table_name='FOO';

Request 1: Current QGIS Oracle code modified to use more complete SQL

Note: The above works also for materialized views as they are implemented as tables.

Issue 2: Views¶

If we create a view over the FOO table:

create view vw_foo as select * from foo;

We can add primary key metadata as follows:

alter view vw_foo add constraint vw_foo_pk primary key (fid) disable;

But even the modified SQL for finding a PK does not work.

The SQL that I have created that detects any primary key for a view is:

-- Try modified table query
SELECT column_name
FROM all_ind_columns a
INNER JOIN
all_constraints b
ON ( a.index_owner=b.owner AND a.table_name=b.table_name AND a.index_name=b.index_name )
WHERE b.constraint_type='P' AND b.owner='QGIS' AND b.table_name='VW_FOO';
-- no rows selected

-- OK construct more complicated query
select c.owner, c.table_name, d.column_name,
c.constraint_name, c.constraint_type, c.index_name
from user_constraints a
inner join
user_dependencies b
on ( b.name = a.table_name
and b.type = 'VIEW'
and b.referenced_type = 'TABLE')
inner join
user_constraints c
on ( c.owner = b.referenced_owner
and c.table_name = b.referenced_name
and c.constraint_type = 'P'
and c.index_name is not null)
inner join
all_ind_columns d
ON ( d.index_owner= c.owner
AND d.table_name = c.table_name
AND d.index_name = c.index_name
)
where a.table_name = 'VW_FOO'
and a.constraint_type = 'P'
and a.view_related = 'DEPEND ON VIEW';
/*
OWNER TABLE_NAME COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
----- ---------- ----------- --------------- --------------- ------------
QGIS FOO FID FOO_PK P FOO_FID_UIDX
*/

It works.

Request 2: Could SQL to detect view PKs be implemented in QGIS please?

Issue 3:¶

Even if a primary key column is detected, QGIS does not display the column_name in its Layer Dialog.

**Request 3: Can QGIS be fixed to display the primary key column name?

I have attached a worked SQL script for all the cases above.

regards
Simon