Oracle APEX, autonumber not working?

1
votes

I'm new to Oracle, and new to Oracle Application Express.

I'm working through a manual and I'm trying to create a table with 3 fields:

USER_ID: Number, Primary Key, Autonumber

USER_NAME: VARCHAR2, Unique

PASSWORD: VARCHAR2

I used the APEX SQL Workshop wizard to create the table, but when I try to do something like the following:

INSERT INTO Schema.USERS (USER_NAME,PASSWORD) VALUES ('planet','password');

...I get told that a unique constraint on the Primary Key is violated.

A trigger was automatically created by the Table Creation wizard. It looks like this:

create or replace trigger "BI_USERS" 
  before insert on "USERS"               
  for each row  
begin   
  if :NEW."USER_ID" is null then
    select "USERS_SEQ".nextval into :NEW."USER_ID" from sys.dual;
  end if;
end;

I was under the impression this would automatically find the highest numeric value in the USER_ID field of USERS and automatically assign an incremented value to each new INSERT, but this doesn't appear to be happening.

Can anyone suggest what I might be doing wrong?

Many thanks

pt

3
oracleplsqlsequenceoracle-apex

3 Answers

2
votes

Sequences: http://www.techonthenet.com/oracle/sequences.php

(Quoted from linked article)
In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

...

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

You said:

I was under the impression this would automatically find the highest numeric value in the USER_ID field of USERS and automatically assign an incremented value to each new INSERT, but this doesn't appear to be happening.

No. A sequence will simply generate the next value based on the last value and the increment, and is not tied to your data. 

select nvl(max(user_id), 0) + 1 
  into v_next_val 
  from users

Is something that would look at your data and increment the highest USER_ID with 1.

If right now you are getting constraint violations due to the primary key on USER_ID, this might mean you have inserted data into your users table, and this data contained values for the USER_ID column. For instance, say created 3 records and manually assigned a value to USER_ID without using a sequence

insert into users (user_id, user_name) values (1, 'John');
insert into users (user_id, user_name) values (2, 'Jerry');
insert into users (user_id, user_name) values (3, 'Bob');

When you then start using the sequence (or create it only then), the sequence will start at the specified start with value = 1. So doing 

insert into users (user_name) values ('planet');

would generate a constraint violation because sequence.nextval would be 1.

2
votes

As @Tom said, if you've inserted rows in the table which specified a value for USER_ID the use of the sequence was bypassed and you may now have rows in the table with USER_ID values which duplicate values which would be fetched from the sequence; thus, upon inserting new rows you're getting a PRIMARY KEY CONSTRAINT VIOLATION error. To work around this write a little script which "consumes" values from the sequence and just discards them, as in the following:

DECLARE
  n       NUMBER := 122;  -- or however many sequence values you need to consume
  seqVal  NUMBER;
BEGIN
  FOR i IN 1..n LOOP
    SELECT USERS_SEQ.NEXTVAL INTO seqVal FROM DUAL;
  END LOOP;
END;

The other alternative is to drop the sequence and recreate it with the minimum value greater than the maximum value of USERS.USER_ID.

Share and enjoy.

-1
votes

This can be helpful: 

create or replace TRIGGER  "TR_USER"
before insert on "USER"
for each row
begin   
   if :NEW."IDUSER" is null then
     select max("IDUSER")+1 into :NEW."IDUSER" from USER;
  end if;
end;