Foreign key cascade on update dangerous?

In relational databases there's a feature called foreign key cascading. Typically, there's an ID associated with each row, or entry, in a database table. Tables may refer to other tables using a foreign key, which typically refers to the IDs of the rows being linked. There are options to "cascade" on two kinds of operations: delete and update. When a table refers to another table, the table pointing to the other table can be configured to take changes in the ID of the original table and integrate them automatically. This is what happens when ON UPDATE CASCADE is set on a foreign key. When ON DELETE CASCADE is set on a foreign key, deleting rows in the original table will also delete rows that are pointing to that row.

So, it's not hard to imagine situations where cascading on delete would be dangerous.

Now a while ago, I was talking to a database specialist about cascading updates on foreign keys. He mentioned that they're dangerous, so I took his word for it. But I've been thinking about it and I still don't see any way they can be dangerous; the only changes being propagated outward will be changes in the values stored in foreign keys; no rows will be deleted.

I've asked around to get second opinions and examples of situations where this would be bad; for now, I'm just going to avoid cascading updates for future projects.

Popular posts from this blog

Arrays in Visual Basic and classic ASP

For the programmer who is used to C-like syntax, working with arrays in Visual Basic or classic ASP can be aggravating. In this post, I will briefly go over declaring single- and multi-dimensional arrays, then iterating through them — the basic operations that make arrays useful. One-dimensional arrays Let's declare an array with six elements. Dim OneDimArray(5) Yes, that says "5", but it has six elements. When we're going through the elements of this array, we'll start counting from zero and end at five. Iterating through one-dimensional arrays For i = 0 to UBound(OneDimArray) Response.Write(i) Next There will be six elements iterated through. General notes about arrays in Visual Basic So far, we're left with the impression that Visual Basic is a strange language. When we declare arrays in VB, the real size is the declared array size plus 1. If you're used to programming in a C-like programming language such as C++ or Java, it's the declared array
Read more

JavaScript: Checking for undeclared and undefined variables

Up until now, the JavaScript I've written has typically been in pursuit of a bigger goal. This means that I worked around language quirks on the spot and moved on. I never stopped to consider the nitty gritty details of the language for commitment to long-term memory, because I wanted to get a working end product. Recently, someone asked me about the best way to check for undefined JavaScript variables. I found myself at a loss, which was alarming to me since doing these kinds of checks is immensely important, practical, and a frequent fact of everyday programming no matter what programming language you're using. The fact of the matter is that most of the time, I know where my variables are coming from. Something like this would work: var x; if (x) x.some_method(); else alert("we can't use x"); In the vast majority of cases where I had to perform a null check, I knew that the variable was declared somewhere because it was of my own maki
Read more

The costs of configurable settings in your web application

By and large, the received wisdom when it comes to configurability and flexible settings for web applications is that more is better. Making something configurable frees up the engineer from having to make mundane (but necessary) changes that can be left to other people. It's desirable to change the state of software without having to rebuild or redeploy the code. There are a lot of obvious benefits to making software configurable, and these benefits are plain to all. What I'm going to focus on here are the costs of configurability, based on my experience and observations over five years of writing web applications full-time. These costs apply to other kinds of software beyond the realm of web applications. I just choose to limit the scope of discussion to web apps because these costs manifest themselves the most when there's a fast development cycle and rapid iteration. The following is intended to be a comprehensive, itemized breakdown of the costs of making an applicati
Read more