How to make a foreign key in reference to a column of the same table-CodePudding

I am making a database and I need to convert the column SuperDni into a foreign key in reference of the column Dni. I want to delete in cascade, but if I put CASCADE in place of NO ACTION, I get an error:

cycles or multiple cascade paths

Only if I do this I can create the table, but I need to delete the registers in cascade if I delete a key in reference of that key.

For example if I delete the Dni = 1 I need to delete the registers that contain that Dni value in the column SuperDni.

Is there a way to do this? because I am learning to use MySQL and SQL Server and I could do it in MySQL.

CREATE TABLE Empleado
(
    Nombre VARCHAR(20) NOT NULL,
    Apellido1 VARCHAR(20) NOT NULL,
    Apellido2 VARCHAR(20),
    Dni VARCHAR(20) PRIMARY KEY NOT NULL,
    Sexo VARCHAR(1),
    Sueldo INT,
    SuperDni VARCHAR(20),
    Dno INT,

    FOREIGN KEY(SuperDni) 
         REFERENCES Empleado(Dni) 
              ON DELETE NO ACTION 
              ON UPDATE NO ACTION
);

INSERT INTO Empleado VALUES("a", "a", "a", "1", "a", 200, NULL, NULL);
INSERT INTO Empleado VALUES("b", "b", "b", "2", "b", 140, "1", NULL);
INSERT INTO Empleado VALUES("c", "c", "c", "3", "c", 230, "1", NULL);
INSERT INTO Empleado VALUES("d", "d", "d", "4", "d", 110, NULL, NULL);

I need to do it of this form because I am studying the university.

CodePudding user response：

I assume that you're keeping your FOREIGN KEY constraint in place, you cannot fix the issue in a FOR DELETE trigger. FOR triggers (also known as AFTER triggers) fire after the activity has taken place. And a foreign key will prevent a row from being deleted if it has references. Foreign key checks occur before deletion.

So, you need an INSTEAD OF trigger.

CREATE TRIGGER [dbo].[SuperDni_Trigger]
        ON [dbo].[Empleado]
        INSTEAD OF DELETE
    AS
        ;WITH q AS
    (
    SELECT  Dni
    FROM  Empleado
    UNION ALL
    SELECT  tc.Dni
    FROM q
    JOIN Empleado tc
    ON tc.SuperDni = q.Dni
    )
    DELETE
    FROM Empleado
    WHERE   EXISTS
    (
    SELECT  Dni
    INTERSECT
    SELECT  Dni
    FROM q
    )
    
    SELECT * FROM Empleado

And If you want to hold the deleted records, introduce a table variable that holds the list of deleted records.

CREATE OR ALTER TRIGGER [dbo].[SuperDni_Trigger]
    ON [dbo].[Empleado]
    INSTEAD OF DELETE
AS
DECLARE @deletions table (Dni varchar(7) not null);
    ;WITH q AS
(
SELECT  Dni
FROM  Empleado
UNION ALL
SELECT  tc.Dni
FROM q
JOIN Empleado tc
ON tc.SuperDni = q.Dni
)
--introducing a table variable to hold the delete records
insert into @deletions(Dni)
select Dni from q
SELECT * FROM @deletions--to query data from the table variable
--this delete comes last
DELETE
FROM Empleado
WHERE   EXISTS
(
SELECT  Dni
INTERSECT
SELECT  Dni
FROM q
)

SELECT * FROM Empleado