6

I am working on a geodatabase. In some of my features, I would like to add a field, in which I will type the values on my own. On this field no duplicates must be allowed. In other words I want it to be a unique value field. I know ObjectID exists to certain uniqueness of the object, but I am working on a relational db and I want this field to be used as primary key.

I have tried adding a field and through access editing it as a "no duplicates allowed" field, but after that, I cannot edit in arcmap and the message "Warning: Unique index on non-OID field" appears.

6

You are not supposed to edit your personal geodatabase from MS Access since this may corrupt your geodatabase and introduce unexpected behaviour. In file and personal geodatabase, you are working with feature classes and tables each of which already has an unique ID field, i.e., ObjectID.

ObjectID, however, can be easily changed when a feature is updated or modified, so you should not depend on the ObjectID field in your database design. This field is used internally within the ArcGIS. Your option is to create a new field which you will take care yourself and make it a "key" field, however as you probably know each table can only have one unique index and it is already taken for the ObjectID field, that is why you are getting this kind of message. You will need to make sure that the field you will create yourself is unique through custom approach, such as setting up a geodatabase domain with pre-defined values, which user will be able to choose from. You could also use triggers to make sure that the values you enter into the field are unique.

If you need more advanced relational database design, you will need to move to an enterprise geodatabase, because the DBMS provides better support for this kind of operations. I have multiple feature classes with in SQL Server database that have unique constraints.

It is worth noting that, in ArcMap, when you edit attribute fields of an SDE feature class for which you have set the unique constraint in SQL Server, and try to enter an existing value in a field with defined unique constraint, you will get an error message and the value will not be entered.

enter image description here

Then you could use the Attribute Inspector (available from the Editing toolbar > Editor > Editing Options > Attributes tab). Check the Display the attributes dialog before storing new features option. This will allow you to enter attributes prior to writing rows to your database at the DBMS level.

| improve this answer | |
  • thank you very much for your answer. I have a few more qustions though: what do you mean by "you could use triggers"? I would like the user to be free to enter values, but at the same time keep them unique by warning them if the value already exists. This is not possible as far as I understand. – christina d. Jan 31 '15 at 13:42
  • No problem at all. As DBA, you would need to add a constraint for the geodatabase table (which you do with SQL in DBMS of your choice). While having this constraint on, your ArcMap users will get a message I've uploaded in my answer. This will happen when they will try to enter an existing value. If moving to a DBMS is not option (have you thought of free Postgres too?), you could add a gdb domain with pre-defined values provided this will work in your workflow. Triggers I mentioned would let you fire up certain events when user will enter duplicates (triggers are part of any DBMS). – Alex Tereshenkov Jan 31 '15 at 14:06
6

You should create an index with unique constraint on the fields.

Just go to the property page of the table and then the index tab. Click the Add button.

This feature is only supported with ArcSDE SQL SERVER database. So with personal geodatabase it is not possible to have unique field in Arcmap and edit features.

enter image description here

As per Documentation:

Unique indexes The Unique option is not used in file geodatabases and can be left unchecked. The Unique option is available for SQL Server ArcSDE geodatabases; however, it is unavailable on the Add Attribute Index dialog box when the source data is a feature class that is registered as versioned.

When you create an index, you are presented with an option of creating the index as unique. Choose this option if the attribute has unique values in each record. This will speed query execution against this attribute, since the database can stop searching after the first matching value is found.

Note, however, that you cannot edit a feature class that has a unique index on a user-defined field in a personal geodatabase. Also, you cannot edit a feature class that is in a feature dataset with another feature class with a unique index on a user-defined field. When attempting to start editing a personal geodatabase, ArcMap returns this error:

Could not edit any of the map's layers. Check to see if a layer or table contains a unique index on a user managed column. If you have a unique index on a field in a personal geodatabase and need to edit, use ArcCatalog to drop the unique index and re-create it as a nonunique index.

| improve this answer | |
  • I receive a "Warning: Unique index on non-OID field: message after doing this. – christina d. Jan 31 '15 at 13:44
  • That's just a warning. It means you have more that one unique id field in the table. – Farid Cheraghi Jan 31 '15 at 13:45
  • True, but the point is that I am not allowed to edit this feature anymore. – christina d. Jan 31 '15 at 13:50
  • Sorry, You are correct. I've updated my answer. – Farid Cheraghi Jan 31 '15 at 13:56
  • The "versioned" note above is very important! If you already have the table versioned with a bunch of versions in flight you must reconcile and post those first because when you unversion the table you'll lose all the work in those versions because they have to be deleted (or will be deleted) when you do that in order to add the unique constraint. – Josh P 20 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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