MySQL 5.7

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

select version();


version()


5.7.35


create table `work`(work_id char(32) primary key);
CREATE TABLE `requests` (   
`request_id` char(32) NOT NULL,  
`owner` varchar(100) NOT NULL,  
`status` enum('PENDING','ACCEPTED','REJECTED') DEFAULT NULL,   
`work_id` char(32) NOT NULL,   
`response_message` varchar(3000) DEFAULT NULL,   
`created_date` datetime NOT NULL,  
`last_modified_date` datetime NOT NULL,   
PRIMARY KEY (`request_id`),   
UNIQUE KEY `work_id` (`work_id`),   
CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `work` (`work_id`) 
) ;
insert into work values('1');
insert into work values('2');
insert into requests values('1','A','PENDING','1','sdfsdf',current_timestamp,current_timestamp);
insert into requests values('2','A','PENDING','2','sdfsdf',current_timestamp,current_timestamp);
select * from `requests`;
select * from work;


request_id
owner
status
work_id
response_message
created_date
last_modified_date


1
A
PENDING
1
sdfsdf
2021-10-26 07:35:13
2021-10-26 07:35:13

2
A
PENDING
2
sdfsdf
2021-10-26 07:35:13
2021-10-26 07:35:13




work_id


1

2


alter table requests drop index work_id;
Cannot drop index 'work_id': needed in a foreign key constraint

alter table requests drop foreign key requests_ibfk_1;
alter table requests drop index work_id;
alter table requests add CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `work` (`work_id`)
insert into requests values('3','A','PENDING','2','sdfsdf',current_timestamp,current_timestamp);
insert into requests values('4','A','PENDING','3','sdfsdf',current_timestamp,current_timestamp);
Cannot add or update a child row: a foreign key constraint fails (`db_461931377`.`requests`, CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `work` (`work_id`))

select * from requests


request_id
owner
status
work_id
response_message
created_date
last_modified_date


1
A
PENDING
1
sdfsdf
2021-10-26 07:35:13
2021-10-26 07:35:13

2
A
PENDING
2
sdfsdf
2021-10-26 07:35:13
2021-10-26 07:35:13

3
A
PENDING
2
sdfsdf
2021-10-26 07:35:13
2021-10-26 07:35:13

request_id	owner	status	work_id	response_message	created_date	last_modified_date
1	A	PENDING	1	sdfsdf	2021-10-26 07:35:13	2021-10-26 07:35:13
2	A	PENDING	2	sdfsdf	2021-10-26 07:35:13	2021-10-26 07:35:13