It saves you from having to write code to drop child entities manually when getting rid of a parent.įurthermore, it might be considered safer than implementing this in other logic because the database is taking care of transactional consistency, deadlocks, and so forth, so the operation should (bugs permitting) be guaranteed atomic. The existence of ON DELETE CASCADE makes more sense: while PKs shouldn't really change, things do often get deleted. It is at least safer than cascading deletes. Perhaps it was added as a logical step along from ON DELETE CASCADE. In theory your primary key should be static so changes that need cascading shouldn't need to happen. If you find yourself needing this sort of cascaded update then that is perhaps a "code smell" in your database design. 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? That's why I started using "ON UPDATE CASCADE ON DELETE CASCADE" in the first place, after asking and learning about it. 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. For me, it adds a lot of confusion and anxiety. 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. I mean, if something can execute queries in your database, isn't "all lost" anyway? They can just do: DELETE FROM table1/table2 CASCADE 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". 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? 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). I'm basically confused about the entire concept of "ON UPDATE" and "ON DELETE". 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. This seems, on the surface, like what should be the default behaviour. 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. 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"įOREIGN KEY (referenceid) REFERENCES "othertable" (id) This code goes out of its way to explicitly add the technically "unnecessary" part: "ON UPDATE CASCADE ON DELETE CASCADE". Example: CREATE TABLE "test1"įOREIGN KEY (referenceid) REFERENCES "othertable" (id) ON UPDATE CASCADE ON DELETE CASCADE 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). The A.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. These three calls will cause Sequelize to automatically add foreign keys to the appropriate models (unless they are already present). The A.hasMany(B) association means that a One-To-Many relationship exists between A and B, with the foreign key being defined in the target model ( B). The A.belongsTo(B) association means that a One-To-One relationship exists between A and B, with the foreign key being defined in the source model ( A). The A.hasOne(B) association means that a One-To-One relationship exists between A and B, with the foreign key being defined in the target model ( B). In all examples above, A is called the source model and B is called the target model. In other words, the order matters, for the four cases. The order in which the association is defined is relevant.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |