MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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,
  PRIMARY KEY (user_id)      -- some index must exist for to refer on
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
INSERT INTO user VALUES 
(1, 'login 1', 'password 1', 'first name 1', 'last name 1'),
(2, 'login 2', 'password 2', 'first name 2', 'last name 2');
SELECT * FROM user;


user_id
login
password
first_name
last_name


1
login 1
password 1
first name 1
last name 1

2
login 2
password 2
first name 2
last name 2


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,
  INDEX idx_user_id (user_id),     -- must be created before FK creation
  FOREIGN KEY (`user_id`) REFERENCES user(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
INSERT INTO post VALUES
(1, 1, 'comment 1-1', CURRENT_DATE, CURRENT_TIME),
(2, 1, 'comment 1-2', CURRENT_DATE, CURRENT_TIME),
(3, 2, 'comment 2-1', CURRENT_DATE, CURRENT_TIME),
(4, 2, 'comment 2-2', CURRENT_DATE, CURRENT_TIME);
SELECT * FROM post;


post_id
user_id
comment
date_to_add
time_to_add


1
1
comment 1-1
2021-06-02
05:47:51

2
1
comment 1-2
2021-06-02
05:47:51

3
2
comment 2-1
2021-06-02
05:47:51

4
2
comment 2-2
2021-06-02
05:47:51


UPDATE user SET user_id = 11 WHERE login = 'login 1';
DELETE FROM user WHERE login = 'login 2';
SELECT * FROM user;
SELECT * FROM post;


user_id
login
password
first_name
last_name


11
login 1
password 1
first name 1
last name 1




post_id
user_id
comment
date_to_add
time_to_add


1
11
comment 1-1
2021-06-02
05:47:51

2
11
comment 1-2
2021-06-02
05:47:51

user_id	login	password	first_name	last_name
1	login 1	password 1	first name 1	last name 1
2	login 2	password 2	first name 2	last name 2

post_id	user_id	comment	date_to_add	time_to_add
1	1	comment 1-1	2021-06-02	05:47:51
2	1	comment 1-2	2021-06-02	05:47:51
3	2	comment 2-1	2021-06-02	05:47:51
4	2	comment 2-2	2021-06-02	05:47:51