Oracle APEX error on insert row - ORA-00001: exclusive restriction (ASCHEMAXXXXX.Table_name_PK) violated

Question

Why am I getting this error when trying to insert a new row in the APEX app?

ORA-00001: exclusive restriction (ASCHEMAXXXXX.Table_name_PK) violated

There is a dynamic action on page load to get the next value for one of the fields.

I tried to create a sequence and use it on the primary key, but got .nextvalue not declared.

Answer 1

You didn't explain what that dynamic action actually does, but - it seems it doesn't do what you meant it would. Besides, what kind of a page is it? Form? Interactive Grid? Something else? Consider posting as many information as possible; otherwise, it is difficult to guess what might be going on.

---

Saying that you tried to use a sequence, then: if your database version supports it, why wouldn't you switch to an identity column and let database take care about PK values? For example:

SQL> create table test
  2    (id          number generated always as identity primary key,
  3     name        varchar2(20));

Table created.

SQL> insert into test (name) values ('Littlefoot');

1 row created.

SQL> select * from test;

        ID NAME
---------- ----------
         1 Littlefoot

SQL>

---

Another option is to use sequence + trigger combination:

SQL> create table test
  2    (id     number primary key,
  3     name   varchar2(20));

Table created.

SQL> create sequence seq_test;

Sequence created.

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.id := seq_test.nextval;
  6  end;
  7  /

Trigger created.

SQL> insert into test (name) values ('Littlefoot');

1 row created.

SQL> select * From test;

        ID NAME
---------- ----------
         1 Littlefoot

SQL>

Answer 2

I had previously inserted data programmatically, which included specific primary key (PK) values. Consequently, the trigger did not increment the PK sequence number. Therefore, when I attempted to add a row via the form, it tried to assign a PK number that was already in use.