Delete with Insert in PL/SQL with FK error

Asked

Viewed 51 times

1

Good night,

I have a table that calls horariofunc and the primeiry key of this table is linking in the address table.

I happen to be using to update the data a delete and an Insert with the new data. The Insert has the same first key that is being referenced in the address table.

But when starting the transaction, as the delete comes first, goes up an error from my FK Constraint of the address table.

I wonder if there is a way out of this situation, without removing the Constraint and without failing to update the data with delete and Insert, as it becomes simpler a single function to do both rsrs services.

SQL:

 begin  
    delete from cahorariofunc cahf
       where cahf.id_pessoa = 24
     and cahf.cd_horariofunc = 1;
     insert into cahorariofunc
    (id_horariofunc,
     id_pessoa,
     cd_horariofunc,
     cd_horariofunc_erp,
     ds_horariofunc,
     bo_ativo)
  values  (36, 24, 1, '1', 'PRINCIPAL', 'S');

 end;

Note: My Primary key is composed by the person id and working time code.

  • What error ? How is fk’s "on delete" statement ? https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/create-table-foreign-keys.html

  • From what I understand you are deleting and inserting only to not have the trouble of having to check if the information exists and if there is update and if there is no include, correct? If this is the case you could simply use a "merge".

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.