Problems in the relationship of two tables

Question

Can you help me with that ?

Being objective neither father nor children allow exclusion, it is a strong family bond that was created here:

mysql> delete from Routes Where id = 1;

ERROR 1451 (23000): Cannot delete or update a Parent Row: a Foreign key Constraint fails (sgd_panificadora.os, CONSTRAINT os_routes_id_foreign FOREIGN KEY (routes_id) REFERENCES routes (id))

mysql> delete from drivers Where id = 6;

ERROR 1451 (23000): Cannot delete or update a Parent Row: a Foreign key Constraint fails (sgd_panificadora.os, CONSTRAINT os_routes_id_foreign FOREIGN KEY (routes_id) REFERENCES routes (id))

If anyone understands why I can’t delete "drivers" or "Routes", then I’ve reached haha.

I am going to do this exclusion differently now, but I look forward to a better understanding of the situation.

+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(191) | NO | | NULL | |
| email | varchar(191) | NO | UNI | NULL | |
| password | varchar(191) | NO | | NULL | |
| cpf | varchar(191) | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+


+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(191) | NO | | NULL | |
| drivers_id | int unsigned | NO | MUL | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0,00 sec)

Regarding the "show create table" we will have this:

| drivers | CREATE TABLE `drivers` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`cpf` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `drivers_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |



| routes | CREATE TABLE `routes` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`drivers_id` int unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `routes_drivers_id_foreign` (`drivers_id`),
CONSTRAINT `routes_drivers_id_foreign` FOREIGN KEY (`drivers_id`) REFERENCES `drivers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Answer 1

Good evening Christian, it seems to me the problem is that there is a third table (it seems its name is 'os') that has an id_routes column, which is a foreign key coming from the Routes table (and possibly this key does not use Cascade). Thus, there are probably records of route 1 in this third table, which prevent directly deleting a route. In this case, you would first need to delete the records from the table 'os', then delete from within the table Routes. Or optionally, you can drop the foreign key from the 'os' table and create a new one with Scade.