Avoiding Orphaned Records

SFDonovan

If I want to delete a record but want to find out if that record_id is assigned to any other record in my db how do I appraoch this?

So far my login is this:

$sql = "SELECT COUNT (*) as numrows FROM " . $prefix . "_milpacs_members WHERE weapon_id = '$id'"; 
	$row = $db->sql_query($sql);
	$return = $row['numrows'];

I then display:

There are <?php echo $row['numrows'] ?> soldiers who have been assigned this weapon. Deleting this weapon will erase this from their record!

Since my roster is displayed by matching unit_id = unit_id and weapon_id = weapon_id and such in various tables, I could have several members not displayed in my roster if I delete a weapon from my weapon table.

I think I need to enter a weapon_id of 0 into my weapon table and make the name "unassigned" then just update my members table for those who are assigned to the weapon I am attempting to delete and SET them to 0.

Did I explain this right?

Sxooter

The normal way to do this is to use cascading foreign keys. you can get cascading foreign keys with innodb table types, or ANY OTHER DATABASE just about in existence, firebird, postgresql, oracle, mssql, db2, etc...

Without that you'll have to do some kind of join on the two tables to see if there are dependent records, then delete them by hand.

EDIT: Note that you can always just throw a delete against the known key in the subordinate tables, and if you get a 0 changed tuples there weren't any, and if you get >0 changed tuples, there were.