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';
– Daniel Mendes
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.
– hard123
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
– Motta
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.
– Lucas Scott