SQL Day 5: Cascading Referential Integrity Constraint

LO 1: Cascading Referential Integrity Constraint

Prerequisite: Creating Table, Adding Default Constraint

Cascading Referential Integrity constraint allows to define the actions SQL Server should take when use attempts to delete or update a key to which an existing foreign key points.
For example, If you delete the row with id=1 from the tblGender table, then row with id=1 from tblEmployee table becomes an orphan record.



 So, cascading referential integrity can be used to define actions SQL server should take when this happen. By Default, we get an error when performing update or delete operations.

Setting Actions on Update and Delete

If you remember in previous post “Creating Tables & Enforcing Primary and Foreign Key” we had created a Foreign Key relationship as the display in the following screen. Right click on this key and click on Modify.



 A new window will be displayed in which you can see “Insert And Update Specification” at last and on expand this you will see the Delete Rule and Update Rule.



 By Default, No Action is selected and because of this we have seen an error when performing Update and Delete operation.

In Delete and Update Rule we have four options from which we can select what operation need to perform when this happen. The four operations are:

  • No Action
  • Cascade
  • Set Null
  • Set Default
Let’s discuss in details.

No Action: This is the default behavior. No action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and Update and Delete are rolled back.

Set Default: When an attempt is made to update or delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.



 After setting “Set Default” click on the Close button and then press Ctrl + S to save the table and click Yes on Save dialog.



 Now changes are saved and let’s perform the delete operation on the tblGender table.

Delete query get executed. Now see what will happen on the tblEmployee table. For row id=1, 3 has been updated because this is the default value.



 Set Null: When an attempt is made to update or delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

Cascade: When an attempt is made to update or delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are deleted or updated.

Perform same steps as above to set Cascade.

Before running the delete query lets see what both tables contain.



 Now on executing the delete query on the tblGender table for id=3 then all rows in tblEmployee which have match gets deleted.


That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading