My question is about the best way to deal with referential integrity for foreign keys. I'm coding in C# and querying from a MySQL database. I'm at the point of deciding what foreign key restraints to add. Is it better to have the restraints built into the database itself, or to code them, or a mix of both? In my interface, I've been making MySqlTransactions in each method that's updating a table with references to other tables, to make sure everything is updated and deleted when it should be. Theoretically, this should be enough to maintain referential integrity. It seems like an alternative to this is to set all the constraints so that any call to a table with outside references would update automatically. It also seems like any combination of the two is a recipe for disaster, since any cascading update would be in both the code and the database. Is one better than the other, or is there a magic formula for combining them?
Example:
I'll give one of my methods as an example. I have a BankAccount table and a BRN (bank routing number, which you can think of as a bank branch) table. The BRN table consists of its ID, the routing number, and the branch name. The BankAccount table consists of its ID, the account number, and the BRN ID foreign key (among a couple other things that aren't relevant here). If an admin wanted to delete a BRN and all its child account records, that method would look something like this:
public void DeleteBRN(long? brnID)
{
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
try
{
// (1) Begin Transaction
cmd.Transaction = conn.BeginTransaction();
// (2) Delete specified BRN row from database
if (brnID != null)
{
deleteBRN_TransactionHelper(brnID, cmd);
}
else
{
return;
}
// (3.1) Get all child bank accounts of specified BRN ID
List<BankAccount> accounts = GetAccountsFromBRNID(brnID);
// (3.2) Loop through list of accounts and delete each record
for (int i = 0; i < accounts.Count; i++)
{
deleteBankAccount_TransactionHelper(accounts[i].ID, cmd);
}
// (4) Commit changes
// todo check if successful
cmd.Transaction.Commit();
}
catch (Exception e)
{
// handle / rethrow
}
finally
{
conn.Close();
}
}
}
}
OR, I could add constraints within the database so that the changes made to any BRN record will cascade to its children.
I realize this problem could have multiple correct solutions, so maybe a better question is this: How do I decide which direction is best for my project?
