mysql 8.0.27
create table users( user_id int not null auto_increment, name varchar(100), primary key(user_id) ); create table board( board_id int not null auto_increment, title varchar(100), content varchar(100), user_id int, primary key(board_id) );
insert into users(name) values('hajoo1'); insert into users(name) values('hajoo2'); insert into board(title, content, user_id) values('title1', 'content1', 1); insert into board(title, content, user_id) values('title2', 'content2', 2);
update users set user_id = 3 where name = 'hajoo2';
alter table board add constraint ex_fk foreign key(user_id) references users(user_id);
update users set user_id = 3 where name = 'hajoo2'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`board`, CONSTRAINT `ex_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
update board set user_id = 3 where title = 'title2'; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`board`, CONSTRAINT `ex_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
update board set user_id = 1 where title = 'title2';
alter table board add constraint ex_fk foreign key(user_id) references users(user_id) on update cascade;