Maintaining Data Integrity Easily With SQL Server Relationship Cascade Delete & Update Rule

Maintaining Data Integrity Easily With SQL Server Relationship Cascade Delete & Update Rule

SQL Server provides relationship functionality that can be used to maintain data integrity.
Data Integrity means that all related tables data must have correct key value.
Take example of Header & Line tables. Header & Line have one to many relation with the key is Header ID. So each Header ID can have one or more records in Line table.
Please see screen shots:
tb_header Design:

tb_line Design:

Every header ID value inside Line table must exist in Header table. If there is header ID in Line table that does not exist in Header table then that data is junk.

Developers used to write two functions to update header ID or delete header row(s). These two functions are used to update/delete header and related lines.
With help from SQL Server Relationship then developers will only have one function to do that.

SQL Server Relationship with Cascade Delete & Update Rule will delete/update related lines automatically if header got updated/deleted.

Step by step to do Sql Server Relationship with Cascade funtionallity :

  • Create two tables tb_header & tb_line with structure like two pictures above
  • Set primary key in tb_header to HeaderID and tb_line’s primary key to LineID
  • Create new Database Diagram, add both table tb_header and tb_line, make relationship between tb_header & tb_line HeaderID(s)
  • Expand ‘Insert and Update Specification’. Make Delete Rule to Cascade and same with Update Rule
  • Relation has been made and tb_line’s HeaderID becomes a foreign key automatically
  • Finish

Simulation

Lets insert several records to both tb_header & tb_line for simulation.
tb_header sample:

tb_line sample:

I change tb_header’s HeaderID ‘H3’ to ‘H5’ then tb_line’s HeaderID ‘H3’ will change to ‘H5’ automatically.
tb_header update:

tb_line change automatically:

Same will do with delete operation. Delete tb_header’s HeaderID will delete all related lines.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s