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);

Advertisements

One thought on “Oracle APEX : Source tables for forms and tabular forms must have a primary key.

  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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s