Error when deleting an Oracle table reference

Asked

Viewed 215 times

1

I am trying to delete a record from my table TB_CONTATO who is the daughter of the table TB_CLIENTE:

DELETE FROM TB_CONTATO  WHERE ID_CLIENTE = 2999

But it occurs of violation of integrity:

ORA-02292: integrity constraint (SISMET.SYS_C00418600) violated - child record found <br>

This is the daughter table structure (TB_CONTATO):

CREATE TABLE tb_contato (
  id_contato               NUMBER(10,0) NOT NULL,
  id_cliente               NUMBER(10,0) NOT NULL,
  st_nome                  VARCHAR2(50) NOT NULL
)
  STORAGE (
    NEXT       1024 K
  )
/
PROMPT ALTER TABLE tb_contato ADD CONSTRAINT pk_contato PRIMARY KEY
ALTER TABLE tb_contato
  ADD CONSTRAINT pk_contato PRIMARY KEY (
    id_contato
  )
  USING INDEX
    STORAGE (
      NEXT       1024 K
    )
/

PROMPT ALTER TABLE tb_contato ADD FOREIGN KEY
ALTER TABLE tb_contato
  ADD FOREIGN KEY (
    id_cliente
  ) REFERENCES tb_cliente (
    id_cliente
  )
/
GRANT DELETE,INSERT,SELECT,UPDATE ON tb_contato TO sismetop;


I executed the command line according to this example (Oracle Alter foreign table discard key): ALTER TABLE TB_CONTATO DROP CONSTRAINT SISMET.SYS_C00418600 and generated the following error:

ORA-01735: invalid ALTER TABLE option

I searched on some websites for example Oracle PRIMARY KEY, by which I understood nicely I would do the following :

ALTER TABLE TB_CONTATO
DROP CONSTRAINT pk_nome_da_constraints;

In the example above, I don’t have the name of constranin, as I do to eliminate this reference, this dependence between the Daughter table and the Pail table ?

  • Ever tried to figure out which Constraint this is? Try running a query like this: SELECT * FROM user_constraints WHERE CONSTRAINT_NAME LIKE '%SYS_C00418600';

  • Good morning ! @Daniel Mendes executed this command line and discovered that the name of the Constraint is pk_contact, but this name as you can see in the post refers to Primary key id_contact I confess that I am a little confused, because I simply want to remove the reference from the table TB_CLIENTE and not exclude the Primary key.

  • You may have a "delete Cascade" wrong thought maybe , just seeing the whole structure vide https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php

  • In Oracle, Fks by default has the option "ON DELETE NO ACTION" that does not erase FK reference that is related to some PK. Don’t you have another table referenced id_contact? The ORA-02292 error indicates that you tried to delete something that has reference, so another table is referencing the (s) id_contact(s) that will be (no) deleted(s) by its delete command with filter in id_client = 2999.

No answers

Browser other questions tagged

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