I understand what foreign keys are, and have made a point of including them wherever they make sense for all my database tables that I design.
However, something which has always confused me is whether or not I should be explicitly setting the "ON UPDATE" and "ON DELETE" features (in lack of a better term). Example:
CREATE TABLE "test1"
(
id serial,
referenceid integer,
FOREIGN KEY (referenceid) REFERENCES "othertable" (id) ON UPDATE CASCADE ON DELETE CASCADE
)
This code goes out of its way to explicitly add the technically "unnecessary" part: "ON UPDATE CASCADE ON DELETE CASCADE".
Since this is not done by default, there must be a reason for this! After all, the default behaviour is always (or at least should always be) the most commonly needed behaviour:
CREATE TABLE "test2"
(
id serial,
referenceid integer,
FOREIGN KEY (referenceid) REFERENCES "othertable" (id)
)
In the test1 table, as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that the referenced records in the test1 table will either be updated or deleted. This seems, on the surface, like what should be the default behaviour.
In the test2 table, again as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that PostgreSQL will refuse to perform the query if there are records in test2 which reference the ones being modified.
I'm basically confused about the entire concept of "ON UPDATE" and "ON DELETE". Why would one ever want a query to be refused like that? And "CASCADE" isn't even the only option (besides none); there are multiple other values you can use which cause various behaviour (which I don't understand).
Since there is a stated relationship between the tables (through the foreign keys), isn't the whole point that you want them to remain consistent? So why you not want it to "CASCADE" if there are changes made to the "master" table?
This might be similar to how I could never understand why object-oriented programming had "security measures" in the code, disabling you from directly changing or retrieving an object's properties and being forced to go through "getters" and "setters". I mean, if something can execute queries in your database, isn't "all lost" anyway? They can just do:
DELETE FROM table1/table2 CASCADE
... or something like that.
The ON UPDATE/ON DELETE mechanism seems almost like the database engineers could not decide on the best behaviour and instead put this on the user of the product instead. For me, it adds a lot of confusion and anxiety.
It should be noted that I have used the "test2" style code many times in the past, only to realize that I cannot update or delete records where it made sense. That's why I started using "ON UPDATE CASCADE ON DELETE CASCADE" in the first place, after asking and learning about it.
So why isn't this the default and even the only behaviour for a database? Why would you ever want a query to update/delete your "master records" to fail?
ON UPDATE CASCADE
seems to be useful - it allows to maintain the referential integrity without additional explicit operations.ON DELETE CASCADE
... hmm... you must decide what is more important for you - tablesize/diskspace economy or data history save (I prefer the last one - i.e.ON DELETE SET NULL
). – Akina Dec 3 '19 at 5:58