0

I have a problem with a MySQL database. I want to delete user about id=1 using this query "delete from user where user_id =1".

At this moment should be delete a user, and also every his post from table "post". The problem is that user is deleted, but his post in table "post" not delete. EDIT: I try to create a table using InnoDB engine, but show me error.

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `comment` varchar(300) COLLATE utf8_polish_ci NOT NULL,
  `date_to_add` date NOT NULL,
  `time_to_add` time NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES user(`user_id`) ON DELETE CASCADE
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;



CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `login` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `password` tinytext COLLATE utf8_polish_ci NOT NULL,
  `first_name` varchar(30) COLLATE utf8_polish_ci NOT NULL,
  `last_name` varchar(40) COLLATE utf8_polish_ci NOT NULL,
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
kowal20
  • 19
  • 3
  • 1
    You have two tables without any relationship between them. Why would you expect that updating one table should impact the other? – PM 77-1 Jun 01 '21 at 20:35
  • You need to create a foreign key constraint that will cascade your delete, the database doesn't know to do this by default. – Stu Jun 01 '21 at 20:37
  • I changed my code. but now that not working. – kowal20 Jun 01 '21 at 20:37

1 Answers1

0
DELETE
FROM user
JOIN post USING (user_id)

But I'd recommend to create foreign key with ON DELETE CASCADE option - in such case deleting from user deletes all related rows from post.

During create a table 'post' show me error Code error: 150 "Foreign key constraint is incorrectly formed FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci; ` – kowal20

Unrelated error, but I'll answer. See DEMO fiddle.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • During create a table 'post' show me error Code error: 150 "Foreign key constraint is incorrectly formed FOREIGN KEY (`user_id`) REFERENCES user(`user_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci; ` – kowal20 Jun 01 '21 at 20:41
  • @kowal20 Updated. – Akina Jun 02 '21 at 04:49