I have three InnoDB tables, these three have foreign keys.
This is the diagram:
CREATE TABLE IF NOT EXISTS `tbl_member` ( `id_member` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(80) COLLATE utf8_bin NOT NULL, `lastname` varchar(80) COLLATE utf8_bin NOT NULL, `username` varchar(40) COLLATE utf8_bin DEFAULT NULL, `password` varchar(64) COLLATE utf8_bin NOT NULL, `phone` varchar(20) COLLATE utf8_bin DEFAULT NULL, `email` varchar(45) COLLATE utf8_bin DEFAULT NULL, `status` enum('active','inactive') COLLATE utf8_bin NOT NULL DEFAULT 'active', PRIMARY KEY (`id_member`), KEY `username` (`username`,`password`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=107 ; -- -------------------------------------------------------- -- -- Table structure for table `tbl_notification` -- CREATE TABLE IF NOT EXISTS `tbl_notification` ( `id_notification` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Notification ID', `type` enum('notification','announcement') COLLATE utf8_bin NOT NULL DEFAULT 'notification', `title` varchar(255) COLLATE utf8_bin NOT NULL COMMENT 'Title', `notification` text COLLATE utf8_bin NOT NULL COMMENT 'Body', `datestart` int(13) NOT NULL COMMENT 'Date when the notification will start to be deployed', `dateend` int(13) NOT NULL, PRIMARY KEY (`id_notification`), KEY `datestart` (`datestart`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Table structure for table `tbl_notification_member` -- CREATE TABLE IF NOT EXISTS `tbl_notification_member` ( `id_notification_member` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `id_notification` int(10) unsigned NOT NULL COMMENT 'Notification ID', `id_member` int(10) unsigned NOT NULL COMMENT 'Member ID', `status` enum('read','unread') NOT NULL DEFAULT 'unread' COMMENT 'Defines if the notification was viewed already or not', `timestamp` int(15) NOT NULL COMMENT 'Time when the notification was saw', PRIMARY KEY (`id_notification_member`), KEY `id_notification` (`id_notification`), KEY `id_member` (`id_member`), KEY `timestamp` (`timestamp`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Constraints for table `tbl_notification_member` -- ALTER TABLE `tbl_notification_member` ADD CONSTRAINT `tbl_notification_member_ibfk_2` FOREIGN KEY (`id_member`) REFERENCES `tbl_member` (`id_member`), ADD CONSTRAINT `tbl_notification_member_ibfk_3` FOREIGN KEY (`id_notification`) REFERENCES `tbl_notification` (`id_notification`) ON DELETE CASCADE;
So, as you can see if I am deleting one notification, the connection between the notifications and the members will be removed in a cascade, which is surprising, but the problem is that I tried to insert thousands of lines. Is there a way to do this somehow, like a cascade? Using indexes or something like that? (I have other relationships in tables that I delete, for example, in sections, so you can add notifications for only one section and limit the number of users who receive a notification)