SEQUENCES | Primary Key | ID | Foreign Key

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

SEQUENCES | Primary Key | ID | Foreign Key

Post by admin »

Oracle had introduced an identity column in 12C and it is to generate auto sequence no for a primary key in a table. A table can have only a single identity column and it has

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

create table emp2 (
id number generated always as identity nocache
constraint emp_id_pk primary key,
name varchar2(255 char)
);

create table emp (
id number generated by default on null as identity
constraint emp_id_pk primary key,
name varchar2(255 char)
);

create table emp (
id number generated always as identity
constraint emp_id_pk primary key,
name varchar2(255 char)
);

CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 90000 INCREMENT BY 1 START WITH 8000 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ENAME" VARCHAR2(50),
"JOB" VARCHAR2(50),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(4,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE
)

Due to identity columns now you don't need a sequence for primary key.

Trigger

create or replace trigger js_party_biu
before insert or update
on js_party
for each row
begin
if inserting then
Select js_party_s.nextval INTO :new.ID from dual;
:new.creation_date := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updation_date := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end js_party_biu;

VIDEO
https://youtu.be/pDkLHO1C9FU
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests