Hey,

I have two columns. Ride and booking.
In table booking there is the foreign key of ride.
The intention is that 30-day and later bookings will be removed.
If I run this query, I get the error message: mysql 1451 can not delete or update a parent row
The query is:

DELETE FROM booking WHERE DATEDIFF(NOW() ,Date )>30;

I searched the Internet and found this solution:

SET foreign_key_checks = 0;
DELETE FROM booking WHERE DATEDIFF(NOW() ,Date )>30;
SET foreign_key_checks = 1;

The second query does work,
My question is: Is there another way to remove bookings?

thanks in advance

Member Avatar for diafol

I'm assuming when you say this:

I have two columns. Ride and booking.

You mean two tables?

So the booking table has a ride_id (FK/constraint) field. You are removing a booking and it flags up a problem. If it was the other way and you were removing a parent (a ride), then I can understand the issue - you could implement a "delete cascade" which would also remove all records that had a ride_id FK field.

Are you sure it isn't due to the booking (the parent) being deleted - do you have something like a booking_id(FK) field in other tables?

Member Avatar for diafol

Sometimes it is useful to have a status field which could be something as simple as a boolean/tinyint, e.g. 0 = inactive, 1=active. That way you don't need to delete cascaded data. Not deleting cascaded data leaves it orphaned :(

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.