{Oracle} a DELETE statement
angelique1
Posted messages
81
Registration date
Status
Member
Last intervention
-
devMan Posted messages 75 Status Member -
devMan Posted messages 75 Status Member -
Bonjour
Here is your query that is not working, I don't understand why:
I am in the process of deleting all the runners who are part of the Festina team
DELETE
FROM COUREUR
WHERE CodeEquipe IN ( SELECT CodeEquipe
FROM EQUIPE
WHERE NomEquipe ='Festina') ;
Knowing that CodeEquipe is a primary key in the EQUIPE table and a foreign key in the COUREUR table
In the error, they tell me it is a violation of integrity constraint; child exists
Please, it is urgent help me
Here is your query that is not working, I don't understand why:
I am in the process of deleting all the runners who are part of the Festina team
DELETE
FROM COUREUR
WHERE CodeEquipe IN ( SELECT CodeEquipe
FROM EQUIPE
WHERE NomEquipe ='Festina') ;
Knowing that CodeEquipe is a primary key in the EQUIPE table and a foreign key in the COUREUR table
In the error, they tell me it is a violation of integrity constraint; child exists
Please, it is urgent help me
Configuration: Windows Vista Safari 532.0
12 answers
Hi
The structure of a delete with join does not exist! The syntax you used is perfectly valid.
Your problem is that there are rows that depend on certain Festina runners. If you delete these runners, the rows dependent on them will no longer depend on anything... and that’s not good.
It’s similar to wanting to delete the Festina team: you would first delete the runners that depend on it.
So you must first delete the rows that depend on the runners you want to delete... I assume they are in tables you haven't mentioned
Once this deletion is done, you can delete your Festina runners.
So try
The structure of a delete with join does not exist! The syntax you used is perfectly valid.
Your problem is that there are rows that depend on certain Festina runners. If you delete these runners, the rows dependent on them will no longer depend on anything... and that’s not good.
It’s similar to wanting to delete the Festina team: you would first delete the runners that depend on it.
So you must first delete the rows that depend on the runners you want to delete... I assume they are in tables you haven't mentioned
Once this deletion is done, you can delete your Festina runners.
So try
select fils.constraint_name, fils.table_name, col.column_name from all_constraints pere, all_constraints fils, all_cons_columns col where pere.constraint_name = fils.r_constraint_name and fils.constraint_name = col.constraint_name and pere.table_name='COUREUR' and pere.constraint_type='P'It should list the table(s) and column(s) referencing your runners that block the deletion.
```sql
DELETE COUREUR
FROM COUREUR
INNER JOIN EQUIPE ON COUREUR.codeEquipe = EQUIPE.codeEquipe
WHERE EQUIPE.codeEquipe = 'valeur_codeEquipe';
```
DELETE COUREUR
FROM COUREUR
INNER JOIN EQUIPE ON COUREUR.codeEquipe = EQUIPE.codeEquipe
WHERE EQUIPE.codeEquipe = 'valeur_codeEquipe';
```
Hello,
for the first issue, it is normal to display an integrity constraint violation.
for the second issue, you just need to apply the foreign key constraint.
for the first issue, it is normal to display an integrity constraint violation.
for the second issue, you just need to apply the foreign key constraint.
Hello
try this query
delete *
from courreur
where courreur.codequipe=equipe.codeequipe
and equipe.nomequipe='festina'
good luck
try this query
delete *
from courreur
where courreur.codequipe=equipe.codeequipe
and equipe.nomequipe='festina'
good luck
Several solutions:
Either you remove the constraint on the tables where you have your foreign key...
Or you modify your constraint so that the rows of the tables with your foreign key are also deleted...
https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php
Handle with care!!
See you later
--
By misunderstanding, it could work!!
Either you remove the constraint on the tables where you have your foreign key...
Or you modify your constraint so that the rows of the tables with your foreign key are also deleted...
https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php
Handle with care!!
See you later
--
By misunderstanding, it could work!!
Hello,
In similar situations in MySQL
we use cascading deletion....
It seems to me that you also need to define delete/update cascading on the foreign key..
I'm not sure!!!!
In similar situations in MySQL
we use cascading deletion....
It seems to me that you also need to define delete/update cascading on the foreign key..
I'm not sure!!!!
Let's be a little serious!
Does a standard user have the ability to enable or disable constraints?
Do they have the ability to modify constraints?
Why implement constraints if they can be removed later?
--
- My Clio is broken, help me
- Well! why didn't you buy a Peugeot?
Does a standard user have the ability to enable or disable constraints?
Do they have the ability to modify constraints?
Why implement constraints if they can be removed later?
--
- My Clio is broken, help me
- Well! why didn't you buy a Peugeot?
I didn't say he will remove the constraints ... that's not it
I mean he needs to add the ON DELETE CASCADE and ON UPDATE CASCADE properties to the foreign key ..
and when he deletes a primary key, it will automatically delete its foreign keys...
Deletion of the 'parent' => Deletion of the 'children' (cascade)
if he has the rights of course ....
I mean he needs to add the ON DELETE CASCADE and ON UPDATE CASCADE properties to the foreign key ..
and when he deletes a primary key, it will automatically delete its foreign keys...
Deletion of the 'parent' => Deletion of the 'children' (cascade)
if he has the rights of course ....
Hello
First: we don't know if Angelique1 has the right to modify the constraints on the tables used
Second: cascading is a bad habit: the day we accidentally delete a client, we automatically delete their quotes, orders, deliveries, invoices, and payments... quite the deal.
The solution here remains to clean up in participants and then run the initial query again.
--
Happy new year
First: we don't know if Angelique1 has the right to modify the constraints on the tables used
Second: cascading is a bad habit: the day we accidentally delete a client, we automatically delete their quotes, orders, deliveries, invoices, and payments... quite the deal.
The solution here remains to clean up in participants and then run the initial query again.
--
Happy new year
Good evening. When I created the user, I gave it all the privileges.
As for the participant table, the primary key from the runner table migrates to the participant table.
What is the structure of (on delete cascade)? Well, when I created my tables, I declared my constraints normally without implementing the delete on cascade, so please tell me how I could add it.
As for the participant table, the primary key from the runner table migrates to the participant table.
What is the structure of (on delete cascade)? Well, when I created my tables, I declared my constraints normally without implementing the delete on cascade, so please tell me how I could add it.
And if the database refuses to delete the Festina runners, it is simply because there are still some of these runners who are present (or rather referenced) in the PARTICIPANT association (which you have never mentioned to us).
I allow myself to send you back to read once again the chapter "Referential Integrity". Once you have understood it, everything will seem much clearer to you.