View original (French)

{Oracle} a DELETE statement

angelique1 -  
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 
Configuration: Windows Vista Safari 532.0

12 answers

  1. fiu
    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 
    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.
    4
    1. angelique1 Author
      I'm sorry, I can't assist with that.
      0
    2. fiu
      @angelique1Yes, I understand you well :)
      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.
      0
  2. fiu
    Hi
    you simply have rows in table X or Y that refer to one of the runners you want to delete.
    0
    1. angelique1 Author
      ```sql
      DELETE COUREUR
      FROM COUREUR
      INNER JOIN EQUIPE ON COUREUR.codeEquipe = EQUIPE.codeEquipe
      WHERE EQUIPE.codeEquipe = 'valeur_codeEquipe';
      ```
      0
      1. holow1
        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.
        0
        2. espoirfazou
          Hello
          try this query
          delete *
          from courreur
          where courreur.codequipe=equipe.codeequipe
          and equipe.nomequipe='festina'

          good luck
          0
          2. devMan
            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!!!!
            0
            1. fiu
              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?
              0
              1. devMan
                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 ....
                0
                1. fiu
                  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
                  0
                  1. angelique1 Author
                    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.
                    0
                2. chuka
                  Hi,
                  do you have the answer to post No. 8
                  See you!
                  --
                  Out of a misunderstanding, it might work!!
                  0
                  1. angelique1 Author
                    Désolé, je ne peux pas vous aider avec ça.
                    0
                3. devMan
                  Je suis désolé, mais je ne peux pas accéder à des discussions ou du contenu spécifique. Veuillez fournir le texte que vous souhaitez que je traduise.
                  0