U7iWGwix

1how to define foreign key constraints
2Requests -  request_id, request_message, user_id
3Responses - response_id, response_message, user_id
4users -     user_id, user_name
5	
61. If user_id is not present in Users table, and someone is inserting the data in Requests or Responses for that user_id -- then error
72. If request_id is not present in Requests table, then if someone is inserting in responses table for that request_id -- then error 
8
93. If someone deletes an user_id, all associated requests and responses with that user_id should be deleted automatically.
104. If someone deletes an request_id, all the associated responses with it, should be deleted automatically.
11	
12CREATE TABLE IF NOT EXISTS `reponses` (
13  `response_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
14  `response_message` varchar(45) DEFAULT NULL,
15  `user_id` int(10) unsigned NOT NULL,
16  PRIMARY KEY (`response_id`,`user_id`),
17  KEY `fk_reponses_users1` (`user_id`)
18) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;
19
20
21CREATE TABLE IF NOT EXISTS `requests` (
22  `request_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
23  `request_message` varchar(45) DEFAULT NULL,
24  `user_id` int(10) unsigned NOT NULL,
25  PRIMARY KEY (`request_id`,`user_id`),
26  KEY `fk_requests_users` (`user_id`)
27) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;
28
29
30CREATE TABLE IF NOT EXISTS `users` (
31  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
32  `user_name` varchar(45) DEFAULT NULL,
33  PRIMARY KEY (`user_id`)
34) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;
35
36
37ALTER TABLE `reponses`
38  ADD CONSTRAINT `reponses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION;
39
40ALTER TABLE `requests`
41  ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION;