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

Question

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.

You can't have a self referencing foreign key with cascaded delete, because as the error says it could potentially cascade forever. Also when posting errors on this site please post them verbatim, not in your own words. The problem is, what if you parent has 2 children and you only delete one of them? You don't want to delete the parent then. So maybe you only want to delete the parent when all children have been deleted. In which case you probably best off putting that logic in a stored procedure. — Dale K
– Dale K, Commented Sep 14, 2021 at 3:47
Sorry, I see now, you want to delete the child with the parent. So an SP or trigger is the way to go. SP's provide better visibility and maintainability. — Dale K
– Dale K, Commented Sep 14, 2021 at 3:53
I think the error accurately describes and explains your problem, for which there is no solution. — Bohemian
– Bohemian ♦, Commented Sep 14, 2021 at 4:15

sai deepthi kola · Accepted Answer · 2021-09-14 07:05:41Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related