Delete/Modify row in one table based on a condition I have a table structure like

 create table EMPLOYE (
    CodeEmploye varchar2(100) not null,
    NAS varchar2(100),
    CONSTRAINT employe_pk primary key (CodeEmploye)
);

create table SALAIRE (
    CodeEmploye varchar2(100) not null,
    Mois number not null,
    CONSTRAINT salaire_pk primary key (CodeEmploye, Mois),
    CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye) REFERENCES EMPLOYE(CodeEmploye)
);


I want to add a constraint where I should not be allowed to modify/delete a row in EMPLOYE table if the same employee exist in SALAIRE table.

What is the best way to do that ?
Prevent Delete

Add "on delete set null" to the FK constraint.  Since the column is NOT NULL, this process can't happen.

 CONSTRAINT salaire_code_employe_fk FOREIGN KEY(CodeEmploye)
    REFERENCES EMPLOYE(CodeEmploye)
    ON DELETE SET NULL


Prevent Modify

What you'll need to do is check for "can you update?" during the UPDATE process. (eg within a trigger)

One method would look something like:

declare
  type CodeEmploye_nt_t is table of SALAIRE.CodeEmploye%type;

  CodeEmploye_nt   CodeEmploye_nt_t;
begin
  select a.CodeEmploye
    bulk collect into CodeEmploye_nt
  from SALAIRE a
  where a.CodeEmploye in ( new.CodeEmploye, old.CodeEmploye )
  FOR UPDATE; -- prevent other from modifying important SALAIRE rows
              -- while you're in the middle of a transaction

  raise some_error;
exception
  when no_data_found then
    null;
end;