Using primary keys with ArcSDE (Oracle)?

Question

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 primary 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 sequences? 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.

• OptimizePrime: 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.

Answer 1

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

• Vidar: Should I be concerned with USER_SDO_GEOM_METADATA if I add my own primary key?
• Mapperz: 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
• Vidar: 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: Ahh - I see the doc provides most answers - a case of RTFM! Cheers.