Following is the way to do the primarykey & foreign key validation before the data is actually inserted or updated in tables.
following are the steps
Assumption is that at oracle side the table is having a proper defination of primary key and foreign key.
1)
create global temporary table pkfk_chk_columns (
col_name varchar2(30),
col_label varchar2(40),
col_value varchar2(1000)
) on commit delete rows
2) compile the attached procedure
create or replace procedure PKFK_CHK(tablename VARCHAR2,
PKey_FKey VARCHAR2,
retval OUT VARCHAR2,
errmsg OUT VARCHAR2) is
-- PKey_FKey := 'PK' or 'FK'
From any frontend application before you submit the request to save the data in oracle tables, you need to insert records in table pkfk_chk_columns
col_name :- Name of the field (in UPPERCASE)
col_label :- Label of the field
col_value :- VALUE of the field
For Primary Key check
Insert records in pkfk_chk_columns related to primary key fields
Call the stored procedure PKFK_CHK by passing relevant values
- tablename on which the primary key or foreign key needs to be checked
- PK :- Primary Key check
- retval and errmsg will be error code and userfriendly error message which can be thrown to enduser from the frontend application.
For Foreign Key check
Insert records in pkfk_chk_columns related to foreign key fields
- tablename on which the primary key or foreign key needs to be checked
- FK :- Foreign Key check
- retval and errmsg will be error code and userfriendly error message which can be thrown to enduser from the frontend application OR Pass the retval and error message to application error handler routine.
DO not insert records into this table if data is NULL or blank for the given field.
SO NO MORE ORA-xxx errors related to primary key or foreign keys..
