Cascading referential integrity constraint - Part 5

hello welcome to presume technologies I am venket this is part 5 of sequel server in this session we'll understand what is cascading referential integrity constraint it's all about now to understand this session properly I strongly recommend watching parts 3 & 4 in part 3 we have seen how to create tables and add primary and foreign key constraints and in part 4 we have seen how to add a default constraint if you remember we have been working with tables TBL person and TBL gender in parts 3 & 4 and we have marked the standard ID column as a foreign key column within this TBL person table so if I ask you what is the gender of Jade what will you do you will check the value of this gender ID column go to TBL gender table check ok number two is female so Jade's gender is female now let's imagine what's going to happen if I delete this female record from TBL gender table obviously Jade and Mae records will have no meaning so if I ask you what is Jade's in Mae agenda number two we don't have a corresponding record here so we will not be able to tell their gender so these two records Jade and Mae will become orphan records that's why by default sequel server doesn't allow these kinds of deletes okay let's flip back to sequel server management studio and if you look at the sample data that we have within these two tables now if you look at Simon he is I mean the gender of Simon is 2 which is female now let's imagine what's going to happen if I delete this female record from TBL gender table mind you in TBL person there are still rows referring that value so obviously when I try to delete I get an error okay look at this the error says there is you know the delete statement conflicted with the reference constraint TBL person gender FK okay so what is this TBL person gender FK if you remember in part 3 of this Cirie's we have created a foreign key constraint with this name TBL person gender FK so what is this constraint saying this constraint is basically saying so if we expand this table tables and column specifications so in TBL person in TBL person the gender ID column is referring to ID column in TBL gender so that's what we have said using this foreign key constraint okay now if you look you know if you scroll down a little bit in this foreign key relationships window there is this insert and update specification and by default if you look at the delete and update rule it is set to no action so the default is no action so if it is set to no action and when you try to delete any of the existing rows when you try to delete any of the existing rows for which there are references in TBL person obviously if we allow that row to be deleted these rules will become orphan records and they will and that kind of a delete will not be allowed by default okay which is no action so if you remember for specifying the cascading referential integrity you know you have four options the default is no action in which case you get an error and the statement will be rolled back okay but you can also specify cascade set null and set default first let's look at set default now if you remember in the previous session we have seen how to set a default it using a default constraint so if you look at the constraint that we have already on TBL person table so it's called default on TBL person on gender ID column and we specified that if somebody doesn't supply a value for the gender ID column then we want to use a default of 3 that's what this default constraint us and if you're not sure how to set up a default constraint I strongly recommend watching the previous session in this video series okay now what you can basically do is in this file foreign key constraint vendor can insert an update specification section let's say if somebody tries to delete a row I want to set it to a default click OK save the changes now if somebody tries to delete ok we already have a female record I mean we have this female record here if somebody tries to delete this female record from TBL gender table we said any dependent rows you know set it to default and the default that we have here is it it's gonna set that to 2 to 3 ok so now let's try to delete this and see what's going to happen and we select the data back you should see Simon gender ID set to default which is 3 okay and along the same lines you have other options as well you can either cascade the delete and update operation or you can set to now ok let's see how set null works so obviously if you want to change the update and delete cascade operation so you come to the insert and update specification in the delete rule let us say I want to set null instead of set default close and save it now let's go let's go back to the tables so in TBL person table if you look at this there are let's say John and rich they are the gender is male now let us say I want to delete this male record so what did we specify in cascading referential integrity we said you know use set null okay so when I delete this male record from this table from the primary key table what's going to happen for the dependent rows the gender ID for the dependent rows will be set to now so in this case John and rich fros will be set to now so let's execute that now let's select the data back so John and Ray gender ID is set to now okay and a final option that we have is cascade okay as the name suggests we want to cascade the update and delete operation to the dependent table okay so how do we do that go into the foreign key constraint window and insert and update specification section instead of set now we will say cascade close that save now if you look at in TBL gender we have number three unknown and there are three people Simon Sarah and Johnny whose gender ID is unknown now if I go ahead and delete this record from TBL gender table all the dependent rows will also be deleted from TBL person table so in this case these three rows will be deleted okay so gone all you know those three rows are gone now okay so basically you know there are four options available for you when setting up cascading referential integrity constraint the default is no action you know if you said this option by default you get an error and the update and delete statements will be rolled back otherwise you can cascade the updates and deletes or you can set null you know the dependent rows can be set to none or they can be set to defaults here you can find resources for asp.net and C sharp interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 687,830

Rating: 4.8883634 out of 5

Keywords: cascading, referential integrity constraint, sql server, example, referential integrity example, referential integrity constraint in dbms with example, referential integrity constraint in relational database, referential integrity constraint in database, sql referential constraint, sql referential integrity constraint violation, referential integrity sql server, referential integrity in database with example, create table referential integrity sql

Id: ETepOVi7Xk8

Length: 8min 19sec (499 seconds)

Published: Sun Aug 05 2012