Delete in ERREOR with an FK on the same table

• abdalah.mehdoini

  Ten Centuries

  Points: 1012

  More actions

  May 6, 2023 at 7:55 am

  #4185141

  Hello

  I do not know why my delete does not work despite I respect my FK constraint

  The FK constraint is based on the same table

  ALTER TABLE [dbo].[EVACUATION]  WITH NOCHECK ADD  CONSTRAINT [FK_EVA_DECH] FOREIGN KEY([COD_EXP_CHG], [COD_CEL_CHG], [NUM_DOS_CHG], [NUM_EVT_CHG])
  REFERENCES [dbo].[EVACUATION] ([COD_EXP], [COD_CEL], [NUM_DOS], [NUM_EVT])
  GO
  
  ALTER TABLE [dbo].[EVACUATION] CHECK CONSTRAINT [FK_EVA_DECH]
  GO

  Here is my delete statement

  DELETE E2
                              FROM EVACUATION E2
                                   INNER JOIN Purge.Purge_Select_dossier P      ON P.COD_EXP = E2.COD_EXP_CHG
                                                                                AND P.COD_CEL = E2.COD_CEL_CHG
                                                                                AND P.num_dos = E2.num_dos_CHG
                              WHERE EXISTS
                              (
                                  SELECT 1
                                  FROM EVACUATION E
                                  WHERE E.cod_exp = E2.COD_EXP_CHG
                                        AND E.cod_cel = E2.COD_CEL_CHG
                                        AND E.num_dos = E2.NUM_DOS_CHG
                                        AND E.NUM_EVT = E2.NUM_EVT_CHG
    );

  I always have the error that my FK constraint is not respected

  

  Thanks for your help

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 6, 2023 at 8:14 am

  #4185153

  The only rows you will be able to delete are those which have no 'children'.

  DELETE EVACUATION E2 
  WHERE NOT EXISTS 
  (
  SELECT 1 from EVACUATION E WHERE E.COD_EXP_CHG = E2.COD_EXP AND ...
  )

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• May 6, 2023 at 1:16 pm

  This was removed by the editor as SPAM