0

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

2
Contributors
2
Replies
13
Views
2 Months
Discussion Span
Last Post by diafol
0

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?

0

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 :(

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.