Oracle APEX : Source tables for forms and tabular forms must have a primary key.

While creating a’ Report and Form’ Application in Oracle APEX if your using a table which has no primary key then you might get an error.  I would not recommend to do the below procedure in a production environment. It is better to create a new table with a primary key column  and copy the existing tables data into it.
But if you want to modify the existing table by adding a new column for primary key then follow the below method.
Suppose your table is called “your_table”
— Add the Column which will be Primary Key

ALTER TABLE your_table ADD (pk_id NUMBER);

— Create a Sequence

CREATE SEQUENCE  your_table_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

— Create a Trigger to update the PK_ID column with next value in sequence

create or replace TRIGGER  your_table_trg
before insert on your_table
for each row
begin
if :NEW.”ID” is null then
select your_table_seq.nextval into :NEW.”ID” from dual;
end if;
end;
/

— Fill the Sequences in Primary Key Column with Sequence

update your_table set pk_id = your_table_seq.nextval;
commit;

— Add Primary Key

ALTER TABLE your_table ADD CONSTRAINT your_table_pk PRIMARY KEY (pk_id);

———————- EXAMPLE ———————–

ALTER TABLE cardholder_bkp ADD (pk_id NUMBER);
CREATE SEQUENCE  cardholder_bkp_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
create or replace TRIGGER  cardholder_bkp_trg
before insert on cardholder_bkp
for each row
begin
if :NEW.”ID” is null then
select cardholder_bkp_seq.nextval into :NEW.”ID” from dual;
end if;
end;
/
update cardholder_bkp set pk_id = cardholder_bkp_seq.nextval;
commit;
ALTER TABLE cardholder_bkp ADD CONSTRAINT cardholder_bkp_pk PRIMARY KEY (pk_id);

Category: DatabaseSQL

Tags:

0 comments

  1. Reblogged this on Easy SQL DBA and commented:

    Re-posting after a year, since had a similar issue on a production database. For creating replication of a table, i needed a primary key on a column. Without touching the existing columns, we can had an extra column and fill it with sequences via a trigger.

Leave a Reply

Article by: Shadab Mohammad