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