![]() 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). Please refer to the below T-SQL script which creates a parent, child table and a foreign key on the child table with DELETE CASCADE rule.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. ![]() Similarly, we can create a foreign key with UPDATE CASCADE rule by selecting CASCADE as an action for the update rule in INSERT and UPDATE specifications. Once you click on Yes, a foreign key with delete rule is created. In the INSERT and UPDATE specifications, select Cascade for the delete rule.Ĭlick on Close and save the table in the designer. select the foreign key column in the child table. Select the parent table and the primary key column in the parent table. Right click on the Keys folder and select New Foreign Key.Įdit table and columns specification by clicking … as shown in the below image. Login to the SQL Server using SQL Server Management Studio, Navigate to the Keys folder in the child table. Using the SQL Server Management Studio GUI: Let us see how to create a foreign key with DELETE and UPDATE CASCADE rules along with few examples.Ĭreating a foreign key with DELETE and UPDATE CASCADE rules Triggers on a table with DELETE or UPDATE cascading foreign key.Creating DELETE CASCADE and UPDATE CASCADE rule in a foreign key using T-SQL script.Creating DELETE and UPDATE CASCADE rule in a foreign key using SQL Server management studio.We will be discussing the following topics in this article: UPDATE CASCADE: When we create a foreign key using UPDATE CASCADE the referencing rows are updated in the child table when the referenced row is updated in the parent table which has a primary key. In this article, we will review on DELETE CASCADE AND UPDATE CASCADE rules in SQL Server foreign key with different examples.ĭELETE CASCADE: When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |