Giving values to primary key

Let us see how we might provide a value to the primary key in Oracle. There are many ways to provide a value for the primary key. One is to create a trigger (see http://van-maanen.com/index.php/2011/02/02/create-a-surrogate-key-in-oracle/ ). Another is to declare a field as an identity field (id NUMBER GENERATED ALWAYS AS IDENTITY,) . A third way is to create a sequence that is used to load unique values into that primary key field.

Let me provide an example to load such field. The field is loaded with the NEXTVAL field of a sequence. Then the value is created first. Then the value is loaded into the field that contains the primary key.

DECLARE
  NewUserID   employee.empid%TYPE;
BEGIN
  NewUserID := EmpIDSequence.NEXTVAL;
  INSERT INTO employee (dno, empid)  VALUES( 5, NewUserID);
END;
/

Another example is direct usage in an insert statement of a sequence object. Recently this possibility to use sequence objects in inserts is introduced in Oracle.

INSERT INTO USERS    VALUES ( 'John',    UsrIDSeq.NEXTVAL);