in sample dataset emp and dept.
how to prevent duplicate value of column in interactive grid.
case -1
-------
Requirement of that in emp table emp name should be not duplicate for the same department. for example dept 10 - account i entered sam with employee id 8972 and entered 9872 sam for same dept should be prevent.
how i can do. i tried in process interactive grid - Automatic processing and write plsql to check for insert and update and if okey then i manually insert the record. it prevent the insert. but when i update with same value it done. any alternative solution for that.
at database level i am not be able to enforce constraint.
this is the sample example. in current business application i have in corporate this solution. business application i am unable to explain here so with sample example i explain my problem.
Any approach which relies on front-end validation to stop duplicates is doomed to fail.
While you can create a process to check if there's already a row in the table for the employee & department like this:
insert into employees ( emp_id, dept_id )
select :p1_emp_id, :p1_dept_id
from dual
where not exists (
select * from employees
where emp_id := :p1_emp_id
and dept_id := :p1_dept_id
);this fails if two people run the process at the same time.
You can only view changes committed by other transactions. So if the second starts before the first commits, they'll both go "no existing rows for this emp/dept_id combo - it's fine to insert".
So both can insert the row, leaving you with duplicates.
To avoid this you must create a unique constraint over ( emp_id, dept_id ).
If you're looking to do insert-if-not-exists; update-if-exists logic, you can use MERGE. But this still suffers from the concurrency issue above.