quoted_identifier - Restricción de autorreferencia en MS SQL

t sql set quoted_identifier on

Question

¿Es cierto que MS SQL restringe las restricciones de autorreferencia con la opción ON DELETE CASCADE? Tengo una tabla con relación padre-hijo, la columna PARENT_ID es clave externa para ID. Crearlo con la opción ON DELETE CASCADE provoca un error

"La introducción de la restricción FOREIGN KEY puede causar ciclos o múltiples rutas en cascada. Especifique ON DELETE NO ACTION o ON UPDATE NO ACTION, o modifique otras restricciones FOREIGN KEY".

No puedo creer que tenga que eliminar esta jerarquía en modo recursivo. ¿Hay algún problema, excepto los factores desencadenantes?

Answer 1

CREATE TRIGGER MyTable_OnDelete ON MyTable
INSTEAD OF DELETE
AS 
BEGIN

  SET NOCOUNT ON;

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.ParentId

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.Id

END

Answer 2

Es el caso de que no pueda configurar ON DELETE CASCADE en una tabla con restricciones autorreferenciales. Existe un potencial de problemas de lógica cíclica, por lo tanto, no lo permitirá.

Aquí hay un buen artículo, aunque es para la versión 8 en lugar de 9 de SQL, aunque se aplican las mismas reglas.

Answer 3

Acabo de responder otra pregunta donde esta pregunta estaba vinculada como duplicada. Creo que vale la pena ubicar mi respuesta aquí también:

Esto no es posible. Puedes resolver esto con un INSTEAD OF TRIGGER

create table locations 
(
    id int identity(1, 1),
    name varchar(255) not null,
    parent_id int,

    constraint pk__locations
        primary key clustered (id)

)
GO

INSERT INTO locations(name,parent_id)  VALUES
 (''world'',null)
,(''Europe'',1)
,(''Asia'',1)
,(''France'',2)
,(''Paris'',4)
,(''Lyon'',4);
GO

--Este disparador usará un CTE recursivo para obtener todos los ID después de todos los identificadores que está eliminando. Estas identificaciones se eliminan.

CREATE TRIGGER dbo.DeleteCascadeLocations ON locations
INSTEAD OF DELETE 
AS
BEGIN
    WITH recCTE AS
    (
        SELECT id,parent_id
        FROM deleted

        UNION ALL

        SELECT nxt.id,nxt.parent_id
        FROM recCTE AS prv
        INNER JOIN locations AS nxt ON nxt.parent_id=prv.id
    )
    DELETE FROM locations WHERE id IN(SELECT id FROM recCTE);
END
GO

- Prueba aquí, prueba con diferentes ID. Puedes probar WHERE id IN(4,3) también ...

SELECT * FROM locations;

DELETE FROM locations WHERE id=4;

SELECT * FROM locations
GO

- Limpiar (cuidado con datos reales!)

if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''locations'')
---DROP TABLE locations;