<!DOCTYPE html><html><head>
<style type="text/css">body { font-family:'Times New Roman'; font-size:13px}</style>
</head>
<body><div>Folks,</div><div><br></div><div>I am using QGIS 2.18.3 to connect to Oracle (12.1) table with a primary key:</div><div><br></div><div><font face="'Courier New',monospace">create table foo (<br> fid number(10), <br> an_attribute varchar2(10),<br> geom sdo_geometry<br>);<br><br>INSERT INTO USER_SDO_GEOM_METADATA (table_name,column_name,DimInfo,srid) VALUES ('FOO', 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 5979462.12680312, 6024838.75881869, 0.005), SDO_DIM_ELEMENT('Y', 2085800.17222035, 2131294.00019577, 0.005)), 2872);<br>commit;<br><br>insert into foo(fid,an_attribute,geom) values (1,'D',SDO_GEOMETRY(2003,2872,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(5992342.97800986,2112457.60086077, 5992462.06832285,2112461.87775511, 5992461.15953203,2112486.86130093, 5992342.06921902,2112482.5844066, 5992342.97800986,2112457.60086077)));<br>insert into foo(fid,an_attribute,geom) values (2,'I',SDO_GEOMETRY(2007,2872,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(5992230.58945511,2112203.40320644, 5992352.0678867,2112207.76605861, 5992351.15909585,2112232.74960443, 5992229.68033619,2112228.38675226, 5992230.58945511,2112203.40320644)));<br>commit;<br><br>create index foo_geom on foo(geom) indextype is mdsys.spatial_index parameters('sdo_indx_dims=2 layer_gtype=MULTIPOLYGON');<br></font></div><div><font face="'Courier New',monospace"><br></font></div><div><font face="'Courier New',monospace">-- Create primary key (lazy method) -> automatically creates unique index with same name<br><strong>alter table foo add constraint foo_pk primary key (fid);</strong><br><br>-- Check Unique Index name same as primary constraint</font></div><div><font face="'Courier New',monospace">-- Query extracted from SQL tracing of QGIS session.</font></div><div><font face="'Courier New',monospace"><br>COLUMN column_name FORMAT A11<br>SELECT column_name <br> FROM all_ind_columns a </font></div><div><font face="'Courier New',monospace"> JOIN all_constraints b ON <strong>a.index_name=constraint_name </strong>AND a.index_owner=b.owner <br> WHERE b.constraint_type='P' AND b.owner='TWEED' AND b.table_name='FOO';<br><br>/*<br></font><strong><font face="'Courier New',monospace">COLUMN_NAME<br>-----------<br>FID</font></strong><font face="'Courier New',monospace"><br>*/<br><br>-- Now we know we have a QGIS compliant primary key name, connect QGIS to Oracle and check if primary key name appears</font></div><div><font face="'Courier New',monospace">--> No Primary key displayed.<br><br><strong>-- Can anyone tell me why the name of the primary key is not being returned?</strong><br><br></font>
<font face="'Courier New',monospace"><strong>-- </strong></font>
<strong><font face="'Courier New',monospace">If any developer is listening, the QGIS query that finds the column_name of the primary key does not cope with situations where a unique index is created BEFORE the primary key is defined as follows:</font></strong></div><div><strong><font face="'Courier New',monospace"><br></font></strong></div><div><strong><font face="'Courier New',monospace">-- Create primary key using more flexible approach</font></strong></div><div><strong><font face="'Courier New',monospace"><br></font></strong><font face="'Courier New',monospace">alter table foo drop constraint foo_pk;<br>create unique index <strong>foo_fid_uidx</strong> on foo(fid);<br>alter table foo add constraint <strong>foo_pk</strong> primary key (fid) <strong>using index foo_fid_uidx</strong>;<br><br>-- Execute normal QGIS SQL:<br>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='TWEED' AND b.table_name='FOO';<br></font></div><div><font face="'Courier New',monospace"><br></font></div><div><font face="'Courier New',monospace">--> no rows selected.</font></div><div><font face="'Courier New',monospace"><br>-- Actual query should be:<br>SELECT column_name <br> FROM all_ind_columns a <br> INNER JOIN<br> all_constraints b <br> ON ( a.index_owner=b.owner AND a.table_name=b.table_name AND <strong>a.index_name=b.index_name</strong> )<br> WHERE b.constraint_type='P' AND b.owner='TWEED' AND b.table_name='FOO';<br>/*<br>COLUMN_NAME<br>-----------<br>FID<br>*/<br></font></div><div><font face="'Courier New',monospace"><br></font></div><div>
<font face="'Courier New',monospace"><strong>-- </strong></font>
<font face="'Courier New',monospace"><strong>Note: ALTER ADD CONSTRAINT PRIMARY KEY without USING INDEX clause creates the index and then inserts its name (same as constraint) into metadata so that index_name is always constraint_name except when USING INDEX clause is used, then index_name in user_constraints is name of user defined unique index.</strong><br><br>DELETE FROM USER_SDO_GEOM_METADATA where table_name = 'FOO';<br>commit;<br>drop table foo purge;</font></div><div><font face="'Courier New',monospace"><br></font></div><div><font face="'Courier New',monospace">regards</font></div><div><font face="'Courier New',monospace">Simon</font><br></div><div id="M2Signature"><div>-- </div><div>Holder of "2011 Oracle Spatial Excellence Award for Education and Research."<br>SpatialDB Advice and Design, Solutions Architecture and Programming,<br>Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional<br>Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.<br>39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.<br>Website: www.spatialdbadvisor.com<br> Email: simon@spatialdbadvisor.com<br> Voice: +61 362 396397<br>Mobile: +61 418 396391<br>Skype: sggreener<br>Longitude: 147.20515 (147° 12' 18" E)<br>Latitude: -43.01530 (43° 00' 55" S)<br>GeoHash: r22em9r98wg<br>NAC:W80CK 7SWP3</div></div></body></html>