The Importance of SQL Server Foreign Keys

IMPORTANCE OF FOREIGN KEYS

Foreign Key is a part of constraints family in SQL Server. You may be familiar with other constraint types like Primary Key constraints, Unique constraints, Default constraints, and Check constraints etc. to help maintaining data integrity. Every constraint serves a specific purpose.

Using Foreign Key, you can manage child table's data on updat/delete. For example. You have one table like OrderMaster and child table like OrderDetail. In whil OrderID is the Primary key in OrderMaster and same is defined as Foreign key in OrderDetail. 

Scenario: Now you need to delete data for particular order = 1

- You will have to delete all records from OrderDetails first and, then delete particular record OrderMaster.

You can define a foreign key as follows

ALTER TABLE <ChildTable>
ADD CONSTRAINT <ForeignKeyName> FOREIGN KEY <ChildColumnName>
REFERENCES <ParentTable>(<ParentColumnName>)
{ON [DELETE|UPDATE] CASCADE}

For e.g. (OrderDetail)

ALTER TABLE ORDERDETAIL
ADD CONSTRAINT FK_ORDERDETAIL_ORDERNO FOREIGN KEY (ORDERNO)
REFERENCES ORDERMASTER(ORDERNO)
ON DELETE CASCADE

Above example declares that ORDERDETAIL row cannot exist without an associated ORDERMASTER row.

The ON DELETE CASCADE option defines that if ORDERNO is going to be deleted from ORDERMASTER, delete all the associated rows from ORDERDETAIL must be deleted.