How to cascade delete from same MySQL table-CodePudding

Is it possible to cascade delete from same MySQL table? I want to cascade delete in a table that references itself in relations.

Example:

I have a comments table that has comment_id and parent_id, where parent_id is the same id in comment_id.

Parent_id could be either another comment_id or null if it's a root comment.

I want to be able to select any node in this tree and delete it. In the process, all child nodes and sub-child nodes should be deleted.

I have tried to alter my table using this query

ALTER TABLE `comment` 
  ADD FOREIGN KEY (`comment_id`) 
    REFERENCES `comment`(`parent_id`) ON DELETE CASCADE ON UPDATE RESTRICT;

but I end up with an error

Cannot add or update a child row: a foreign key constraint fails (zendaya001.#sql-6c1_1044ab, CONSTRAINT comment_ibfk_3 FOREIGN KEY (comment_id) REFERENCES comment (parent_id) ON DELETE CASCADE ON UPDATE RESTRICT)

For reproduction, this is my comment table:

CREATE TABLE `comment` (
  `comment_id` int NOT NULL,
  `post_id` int UNSIGNED NOT NULL,
  `user_id` int NOT NULL,
  `parent_id` int DEFAULT NULL,
  `content` text NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `count_replies` int NOT NULL DEFAULT '0',
  `count_likes` int NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `comment`
--
ALTER TABLE `comment`
  ADD PRIMARY KEY (`comment_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `post_id` (`post_id`),
  ADD KEY `parent_id` (`parent_id`);

CodePudding user response：

You try to create the foreign key with incorrect direction.

Must be:

ALTER TABLE `comment` 
  ADD FOREIGN KEY (`parent_id`) 
    REFERENCES `comment`(`comment_id`) ON DELETE CASCADE ON UPDATE RESTRICT;

https://dbfiddle.uk/iJRlziwL

comment_id must be defined at least UNIQUE (maybe PRIMARY KEY).

I cannot insert a new record into the table even if ON UPDATE RESTRICT is also set to CASCADE – Freesoul

You do something wrongly.

https://dbfiddle.uk/rcUupB2b