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;