Foreign key and the n-to-n relation

Let's say I have these sql tables:

a) book

b) customer

c) borrow_history (book_id, customer_id ... )

I can define two referencing foreign keys (on delete cascade) from borrow_history, referencing book and customer, so:

• borrow_history records connected to book will be deleted when book was be deleted.
• borrow_history records connected to customer will be deleted when customer was deleted.

But how Do I define foreign keys in a way, that customer will be deleted when he has no borrow_history connected to 开发者_开发技巧him, so deleting a book could recursively delete also customer?

Is it possible, or do I have to use triggers?

You have to use triggers for that.

Anyway user should NOT be deleted only in case he hasn't got any books - other factors like last activity date is important.

I would write a script (and run it with CRON) to delete users without any borrow_history with more than N days from their last action.

And another hint - foreign key in borrow_history pointing books should be set to ON DELETE RESTRICT. If book is borrowed (or was borrowed) it should NOT be deleted