Checking for foreign key references before deleting in MySQL

[RedRocky]

Is there a way, when you have a list of records, to check if each of these records have foreign key references before you attempt to delete any of these records?

 

As an example, if I have a list of borrowers and a list of books, you should not be able to delete a borrower from the system if he still has books on loan. (My actual system is much more complicated than that however - a lot more tables.)

 

I would like to remove the delete option from any borrowers that have books on loan (in that example).

 

If I try to delete a record with foreign key references, I get an error to the effect of:

Database access failed: Cannot delete or update a parent row: a foreign key constraint fails (`dbname`.`tablename`, CONSTRAINT `fkfieldid` FOREIGN KEY (`fieldid`) REFERENCES `tablename` (`fieldid`))

 

 

One solution is to write a query to check if each record, in a list of records, has any foreign key references in any of the possible tables it could be referenced.

 

However, if I wish to display a list of 100 records from a table in my content management system, and I have to run 100 sub-queries in order to display that list, it is obviously very inefficient!

 

End users become confused when they try to delete a record but they can't because that data is 'in use' elsewhere, so I would rather remove the option to delete to avoid confusion.

 

Any ideas on what to do? This must be a problem that others come across.

 

 

[MySQL version: 5.1 (on my development PC).]

[Mchl]

If you're using InnoDB engine, you can set up such constraints and define ON UPDATE and ON DELETE actions.

[fenway]

Also, the entire point on FKs is that you can't delete certain things by accident.

[RedRocky]

Hey thanks for the replies. I'm not sure you fully understand what I am trying to do however.

 

I know the point of FKs, but my issue is that I want the user to know whether a record can be deleted or not, before they attempt to delete it. If a record cannot be deleted, I want to remove the option to delete it.

 

If I have a list of records, I only want to give the option to delete each record IF that record can be deleted. If the record cannot be deleted, I would rather display something like "in use" instead of a delete button or delete checkbox.

 

A solution that I have been made aware of would be to use left outer joins to the tables that contain the foreign keys. In my system however, there could be 10 or so foreign key fields in various tables that I need to check, so I am not sure how efficient this would be.

 

Here is example code of how this would work:

 

select p2.person_id, p2.surname, p2.count1, count(table2_id) as count2 
from
(select p1.person_id, p1.surname, count(table1_id) as count1
from people1 p1 left outer join table1 t1 on p1.person_id = t1.person_id
group by p1.person_id) p2
left outer join table2 t2 on p2.person_id = t2.person_id
group by p2.person_id

 

Any suggestions on how I can make this SQL better?

Thanks.

[Mchl]

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

[RedRocky]

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Hi Mchl, why are you pointing me here?

Any comments on my question?

Thanks.

[Mchl]

I guess I did not read you carefully enough

 

Using left joins (all left joins are outer) is a good solution to find which records can not be deleted. If done well it should be pretty fast.

 

Other possible solution is to create additional TINYINT field in this table, that holds information if given row har or has not dependent rows in other tables. This solution makes for simpler (and faster) queries, but instead requires additional procedures for updating this 'watchdog' field (TRIGGERS, transactions). I'm pretty sure it's also more prone to bugs.

[RedRocky]

What I have done is to create queries that check each table that has a foreign key for the field in question. I then join those queries using UNION, and do a left outer join with the table containing the primary key. I am hoping that this solution will not be too inefficient.