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:
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
Same will do with delete operation. Delete tb_header’s HeaderID will delete all related lines.