Unable to delete or update parent row

Question

I am a new user. I would like to ask about an error I encountered in my homework project. I create room_type_id which is the primary key in the room type table. room_type_id is then included as a foreign key in the room table. The problem is if I enter one of the room type IDs as RI2 instead of RT2. When I try to change it in an update query in the room table it says Unable to update child row foreign key constraint failed. However, it shows that the parent row cannot be updated, the foreign key constraint in the room type table failed. I'm sorry about my English as it's not my main language

create table room_type (
room_type varchar(20) not null,
room_type_id varchar(10) not null,
primary key(room_type_id));

create table room (
room_no varchar(10) not null,
building_id  varchar(10) not null,
room_type_id varchar(10) not null,
room_price varchar(10) not null,
primary key(room_no),
foreign key (building_id) references
building(building_id),
foreign key (room_type_id) references
room_type(room_type_id));

insert into room_type
values ('Standard Room', 'RT1'),
('Dulexe Room', 'RI2'),
('Super Dulexe Room', 'RT3'),
('Suite Room', 'RT4'),
('Super Suite Room', 'RT5'),
('Executive Suite Room', 'RT6'),
('Presidential Suite Room', 'RT7'),
('Family Room', 'RT8'),
('VIP Room', 'RT9'),
('Ceremony Hall 1', 'RT10'),
('Ceremony Hall 2', 'RT11'),
('Ceremony Hall 3', 'RT12'),
('Meeting Conference Room 1', 'RT13'),
('Meeting Conference Hall 1', 'RT14'),
('Meeting COnference Hall 2', 'RT15');

insert into room
values ('R101', 'B1', 'RT1', '0'),
('R102', 'B1', 'RT1', '0'),
('R103', 'B1', 'RT1', '0'),
('R107', 'B1', 'RI2',  '0'),
('R108', 'B1', 'RI2', '0'),
('R1011','B1', 'RT3', '0'),
('R1012','B1', 'RT3',  '0'),
('R1013','B1', 'RT3', '0'),
('R1014','B1', 'RT3','0'),
('R501', 'B5', 'RT4',  '0'),
('R502', 'B5', 'RT4',  '0'),
('R505', 'B5', 'RT5',  '0'),
('R506', 'B5', 'RT5',  '0'),
('R601', 'B6', 'RT4',  '0'),
('R602', 'B6', 'RT4',  '0'),
('R605', 'B6', 'RT5',  '0'),
('R606', 'B6', 'RT5', '0'),
('R701', 'B7', 'RT6', '0'),
('R702', 'B7', 'RT6', '0'),
('R801', 'B8', 'RT7',  '0'),
('R802', 'B8', 'RT7',  '0'),
('R901', 'B9', 'RT8',  '0'),
('R902', 'B9', 'RT8',  '0'),
('R1001','B10','RT9',  '0'),
('R1002','B10','RT9',  '0'),
('R1101','B11','RT10',  '0'),
('R1102','B11','RT11',  '0'),
('R1103','B11','RT12',  '0'),
('R1201','B12','RT13',  '0'),
('R1202','B12','RT14',  '0'),
('R1203','B12','RT15',  '0');

Update room
set room_type_id='RT2'
where room_no='R107';

Update room
set room_type_id='RT2'
where room_no='R108', building_id='B1';

Update room_type
set room_type_id = 'RT2'
where room_type='Dulexe Room';

Answer 1

The foreign key constraint failed because there is no row containing room_type_id='RT2' in your room_type table

Additionally, if a row in room has a specific room_type_id, that row cannot be changed in the room_type table. To do this, you can perform the following steps:

1. Add a new row containing the correct room_type_id in room_type
2. Update the offending row in room to get the new room_type_id
3. Remove room_type_id wrong row from room_type