Force Arcmap to respect primary key constraints/triggers

Question

We have a feature class that has a primary key constraint(not OBJECTID) on it's database table. This field also has an auto incrementing trigger (BEFORE INSERT)

When our users try to create/add features to this class using arcmap, since it's a non nullable number field, ArcMap adds a default value to it (0), so the second time it tries to put 0, it violates the pk constraint. Not only that but it overwrites the BEFORE INSERT trigger by putting 0 after it triggered.

I haven't been able to workaround this even with indexes.

An idea that I thought could work was making a AFTER INSERT incrementing trigger, or an ON UPDATE trigger that only works if the pk values is 0, but I'm not sure how to make none of these, or if that's even possible/necessary.

Answer 1

As Mentioned by Vince, your issue is that there are two models which are in conflicts.

If you have some kind of constrain, ArcGIS, by default, will not know about it. ArcGIS will assume that this is a standard featureclass, and will follow it's own model. If you want ArcGIS to follow your model, you need to customise ArcGIS so that it follows this model. Unfortunately this can't be done via the GUI

This is usually done by a Custom Class Extension. This will require customization via ArcObjects.

Here are some samples of Class Extensions: https://github.com/esri/arcobjects-sdk-community-samples/tree/master/Net/Geodatabase/TimestampClassExtension

https://github.com/esri/arcobjects-sdk-community-samples/tree/master/Net/Controls/EditingTabbedFeatureInspector

Answer 2

I am not sure I fully understand what you say. But the following is definitely wrong:

Not only that but it overwrites the BEFORE INSERT trigger by putting 0 after it triggered.

Nothing can alter the value of a column once the insert statement has been sent to the database for execution. When that happens, the following events take place in sequence:

1. All BEFORE triggers execute
2. The row is inserted into the table
3. All indexes are updated
4. Referential integrity (and other) constraints are checked
5. All AFTER triggers execute

Here is an example. First create a table and a sequence

create table t1 (
  id number primary key,
  name varchar2(30)
);

create sequence t1_s;

Now define the trigger:

create or replace trigger t1_bi
before insert on t1
for each row
declare
begin
  if :new.id is null or :new.id = 0 then
    :new.id := t1_s.nextval();
  end if;
end;
/
show errors

Try a few inserts

insert into t1 values (0,'A');
insert into t1 values (0,'B');
insert into t1 values (0,'C');
insert into t1 values (0,'D');
commit;

Check the results

SQL> select * from t1;

        ID NAME
---------- ----------
         1 A
         2 B
         3 C
         4 D

4 rows selected.

Because the trigger only replaces the ID value when the input is not 0 and not null, the following

insert into t1 values (42,'E');

retains the value given:

SQL> select * from t1;

        ID NAME
---------- ----------
         1 A
         2 B
         3 C
         4 D
        42 E

5 rows selected.