4

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.

5
  • 1
    The issue is the NOT NULL. If you don't declare it NOT NULL, then your database-provided value will populate. – Vince Dec 21 '16 at 23:14
  • @Vince can a pk even be not null? – Mojimi Dec 21 '16 at 23:15
  • 3
    There's two models at work, and they conflict. I've never used a primary key constraint in 28 years as an Oracle DBA. They're slow and needlessly complicate implementation. If you're inserting rows exclusively with ArcGIS, use ArcGIS-compatible techniques to populate rows. – Vince Dec 21 '16 at 23:27
  • 2
    "Not only that but it overwrites the BEFORE INSERT trigger by putting 0 after it triggered." Sorry, that is just not possible. The before insert triggers executes when you issue the INSERT statement. There is no place where anyone could change that value before the INSERT completes. The only place that can happen is within the BEFORE INSERT trigger itself. Maybe the logic in your trigger is to only fill the primary key column with a number when the value it receives is not null ? Just change that code to also set a new number if the value is 0. – Albert Godfrind Dec 23 '16 at 16:39
  • 2
    "I've never used a primary key constraint in 28 years as an Oracle DBA. They're slow and needlessly complicate implementation." Wow. That's a strong statement. And a false one too. Where did you find out that PKs are slow ? And why do they complicate implementation ? Having proper referential integrity in a data model is essential to maintain - well, integrity. Prevent pesky things like orphan child records and duplicate keys ... – Albert Godfrind Dec 23 '16 at 16:43
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.
3
  • You are correct, I did some tests and now it works? Not sure if something changed, maybe my trigger was wrong somehow, anyway thanks! – Mojimi Dec 23 '16 at 18:29
  • What will happen when that table with the ID as the primary key is registered with the geodatabase? An OBJECTID field will be created right? Isn't the OBJECTID supposed to be the primary key? gis.stackexchange.com/q/222527/62572 – Wilson Dec 24 '16 at 23:55
  • The OBJECTID may still be used by ArcGIS to fetch individual rows. That just means it must contain unique values. That can be enforced with a unique index (or a unique constraint). – Albert Godfrind Dec 25 '16 at 0:12
3

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

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.