Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Is it possible, more advisable to assign a primary key to the OBJECTID column in a featureclass in SDE. I am using Oracle and some of my flat tables (junction tables) need to SEE a primary key in the featureclass table in order to model a M-M relationship correctly.

By default ,no primary keys are made by Oracle when you insert and SDE layer (which makes sense as you are doing this all through ESRI tools anyway) although I know SDE creates an index in SDE, but I did not want to cause any kind of corruption if I start meddling with things on the Oracle side - is there any danger of this?

Before submitting this question I also did a little more reading around the subject, and I seem to be finding that it is bad practice to use OBJECTID as a form of primmary key in any case - so would I be better off creating my own primary key field and then let Oracle maintain this with triggers and sequnces? If I do this, effectively behind SDE's back, will I upset SDE and all it's metadata etc?

UPDATE

I have found this nugget of info from Neil Clemmons - what do you think?

Using the OID may not be a good choice. For instance, let's say you have a road centerline feature class related to a maintenance table using the OID field. If you decide to merge two centerlines into the same feature, ArcMap does this by deleting the two original features and creating a new one. Depending on how you have the relate setup, the records in the related table could be deleted. At the very least, the new feature would not be related to any records in the table because it has a new OID. If you based the relate on another unique field, the new feature would retain the attributes of one of the original features and the relationship with the table would be maintained.

and more Neil Clemmons advice...

You shouldn't use the OID as a foreign key. This field is created and handled by the ArcGIS software and is present so that a unique identifier is always available for the ArcObjects functions that need one. You have no control over the value of this field. If you need such a field for use as a foreign key, you should create one and populate it yourself. This way, the value will stay with a feature throughout the lifetime of that feature.

share|improve this question
1  
A rule of thumb that I use for any ESRI dataset, is to state my own key. The ObjectID (geodatabases) and Feature ID (shapefile) can and do change as the data is manipulated. –  OptimizePrime Jun 22 '12 at 19:12
add comment

1 Answer 1

up vote 5 down vote accepted

Create your own primary key field (like 'id') - if using Oracle Spatial 11g then see the best practices for Oracle Locator and Spatial (11g)

Particularly important

Metadata, tolerance and coordinate systems Every SDO_GEOMETRY column in a table requires an entry in the Oracle Locator metadata dictionary, USER_SDO_GEOM_METADATA. The metadata entry includes the following information: Metadata, tolerance and coordinate systems

Every SDO_GEOMETRY column in a table requires an entry in the Oracle Locator metadata dictionary, USER_SDO_GEOM_METADATA. The metadata entry includes the following information:

Name of the table that contains the column of type SDO_GEOMETRY Name of the column defined with the SDO_GEOMETRY data type Number of axes (dimensions) for the SDO_GEOMETRY column Lower and upper bounds for each axis

http://download.oracle.com/otndocs/products/spatial/pdf/spatial_wp09_bestprac.pdf

share|improve this answer
    
Should I be concerned with USER_SDO_GEOM_METADATA if I add my own primary key? –  Vidar Jun 22 '12 at 14:44
1  
If it a spatial entity then yes.MDSYS.SDO_GEOM_METADATA table. This only applies to spatial queries. If you create a spatial view in an Oracle database you also need to add information to the metadata table for the view to be defined –  Mapperz Jun 22 '12 at 14:51
    
I'm not doing any spatial queries - I'm only trying to get an id for a country and then use that id in a junction table to associate with another entity - thus maintaining a M-M relationship with my data. And I'm not creating any spatial views. –  Vidar Jun 22 '12 at 15:05
    
Ahh - I see the doc provides most answers - a case of RTFM! Cheers. –  Vidar Jun 27 '12 at 16:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.