none
Como eliminar registros de tablas relacionales eliminado padres e hijos a la vez... RRS feed

  • Pregunta

  • Hola a todos y todas, Tengo el siguiente problema…

    Tengo una tabla con registros jerarquicos “Id, Descripcion, IdPadre”…

    CREATE TABLE Ejemplo(

                Id INT PRIMARY KEY CLUSTERED,

                Descripcion VARCHAR(50),

                IdPadre INT FOREING KEY REFERENCES Ejemplo(Id))

     

    Digamos que tengo estos registros…

    1          Una descripcion           NULL

    3          Una descripcion           1

    4          Una descripcion           3

    7          Una descripcion           NULL

    15        Una descripcion           1

    17        Una descripcion           7

    18        Una descripcion           7

     

    Necesito hacer un procedure que recibirá varios Id y tendre que eliminarlos todos, como tiene foreign key primero debo eliminar todos los hijos y luego los registros padres…

    Y estoy tratando de hacerlo de la siguiente manera “esto es solo un ejemplo”…

    DECLARE @tabla TABLE (Id INT)

    INSERT INTO @tabla VALUES (1),(7)

     

    BEGIN TRY

                            --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

                            BEGIN TRANSACTION             

                                       DELETE FROM Ejemplo WHERE IdPadre IN (SELECT Id FROM @tabla);

                                       DELETE FROM Ejemplo WHERE Id IN (SELECT Id FROM @tabla);

                            COMMIT

                END TRY

                BEGIN CATCH

                            IF(@@TRANCOUNT > 0)

                                       ROLLBACK

                            RAISERROR('No se pudo eliminar el registro', 16, 1)

                END CATCH

                                      

    SELECT * FROM ejemplo

     

    Cabe resaltar que el nivel de jerarquia actualmente es de un solo nivel “por lo podría decirse que ahorita no es una jerarquía como tal y seguramente permanezca asi”.

    Y no me deja eliminarlo por las relaciones… Supongo que esto tiene que ver con el nivel de aislamiento o algo asi, pero no he podido solucionarlo…

    Alguna idea?

    miércoles, 8 de junio de 2011 17:40

Respuestas

  • El error que te lanza es el normal cuando intentas eliminar un registro que está relacionado con otro. En tu caso, los registros que cumplen que tienen el padre 1 son los que tienen el ID 15 y 3; 15 no tiene ningún "hijo", pero 3 tiene como "hijo" al ID 4, y así te lo marca SQL Server cuando intentas eliminarle.

    Por tanto, tu error no tiene que ver con el nivel de aislamiento, sino con la integridad referencial. Afortunadamente (para este caso) en SQL Server la integridad referencial se valida al final de la query, con lo que el orden de borrado (dentro de una misma instrucción, claro) es indiferente. Teniendo esto en cuenta, podrías crearte una instrucción que listara todos los registros afectados que cumplan la condición de entrada y eliminarlos en la misma instrucción. Es decir, algo como

        ;WITH n AS
        (
            SELECT Id, IdPadre
                FROM Ejemplo WHERE  Id IN (SELECT Id FROM @tabla)
            UNION ALL
            SELECT e.Id, e.IdPadre
                FROM Ejemplo e JOIN n ON n.Id=e.IdPadre
        )
        DELETE ejemplo WHERE id IN (SELECT id FROM n)

    De este modo la instrucción de borrado no lanza error.

    Por cierto, te recomiendo el artículo de Itzik Ben-Gan sobre acciones en cascada e integridad referencial. Es algo antiguo (se escribió para SQL 2000), pero sigue siendo válido: http://msdn.microsoft.com/en-us/library/aa902684%28SQL.80%29.aspx

    • Propuesto como respuesta Carlos Sacristan jueves, 9 de junio de 2011 7:32
    • Marcado como respuesta AdyIr jueves, 9 de junio de 2011 16:01
    jueves, 9 de junio de 2011 7:32

Todas las respuestas

  • Hola.

    ¿Y qué error recibes?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande

    miércoles, 8 de junio de 2011 21:03
    Moderador
  • Hola.

    ¿Y qué error recibes?


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande


    Hola como estas… Dice:

    (3 row(s) affected)

    Msg 547, Level 16, State 0, Line 14

    The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Ejemplo_PadreId". The conflict occurred in database "MiBaseDeDatos", table "dbo.Ejemplo", column 'PadreId'.

    The statement has been terminated.

    miércoles, 8 de junio de 2011 21:58
  • El error que te lanza es el normal cuando intentas eliminar un registro que está relacionado con otro. En tu caso, los registros que cumplen que tienen el padre 1 son los que tienen el ID 15 y 3; 15 no tiene ningún "hijo", pero 3 tiene como "hijo" al ID 4, y así te lo marca SQL Server cuando intentas eliminarle.

    Por tanto, tu error no tiene que ver con el nivel de aislamiento, sino con la integridad referencial. Afortunadamente (para este caso) en SQL Server la integridad referencial se valida al final de la query, con lo que el orden de borrado (dentro de una misma instrucción, claro) es indiferente. Teniendo esto en cuenta, podrías crearte una instrucción que listara todos los registros afectados que cumplan la condición de entrada y eliminarlos en la misma instrucción. Es decir, algo como

        ;WITH n AS
        (
            SELECT Id, IdPadre
                FROM Ejemplo WHERE  Id IN (SELECT Id FROM @tabla)
            UNION ALL
            SELECT e.Id, e.IdPadre
                FROM Ejemplo e JOIN n ON n.Id=e.IdPadre
        )
        DELETE ejemplo WHERE id IN (SELECT id FROM n)

    De este modo la instrucción de borrado no lanza error.

    Por cierto, te recomiendo el artículo de Itzik Ben-Gan sobre acciones en cascada e integridad referencial. Es algo antiguo (se escribió para SQL 2000), pero sigue siendo válido: http://msdn.microsoft.com/en-us/library/aa902684%28SQL.80%29.aspx

    • Propuesto como respuesta Carlos Sacristan jueves, 9 de junio de 2011 7:32
    • Marcado como respuesta AdyIr jueves, 9 de junio de 2011 16:01
    jueves, 9 de junio de 2011 7:32
  • El error que te lanza es el normal cuando intentas eliminar un registro que está relacionado con otro. En tu caso, los registros que cumplen que tienen el padre 1 son los que tienen el ID 15 y 3; 15 no tiene ningún "hijo", pero 3 tiene como "hijo" al ID 4, y así te lo marca SQL Server cuando intentas eliminarle.

    Por tanto, tu error no tiene que ver con el nivel de aislamiento, sino con la integridad referencial. Afortunadamente (para este caso) en SQL Server la integridad referencial se valida al final de la query, con lo que el orden de borrado (dentro de una misma instrucción, claro) es indiferente. Teniendo esto en cuenta, podrías crearte una instrucción que listara todos los registros afectados que cumplan la condición de entrada y eliminarlos en la misma instrucción. Es decir, algo como

        ;WITH n AS
        (
            SELECT Id, IdPadre
                FROM Ejemplo WHERE  Id IN (SELECT Id FROM @tabla)
            UNION ALL
            SELECT e.Id, e.IdPadre
                FROM Ejemplo e JOIN n ON n.Id=e.IdPadre
        )
        DELETE ejemplo WHERE id IN (SELECT id FROM n)

    De este modo la instrucción de borrado no lanza error.

    Por cierto, te recomiendo el artículo de Itzik Ben-Gan sobre acciones en cascada e integridad referencial. Es algo antiguo (se escribió para SQL 2000), pero sigue siendo válido: http://msdn.microsoft.com/en-us/library/aa902684%28SQL.80%29.aspx

    Hola... Estupendo, funcionio perfectamente, gracias por el query, por el link y por tu tiempo...
    jueves, 9 de junio de 2011 16:01