How to prevent database orphans from appearing?

What is a database orphaned record?

Suppose you have a parent-child relationship between two database tables (for example Orders and OrdersLine. Each record in the OrdersLine table must have one foreign key that is linked to an orderId in the Orders table.

What happens when we do a delete on the Orders table? One of the orderId gets removed.  All the records in the OrdersLine table that referred to this parent record orderId are then "orphaned"; i.e., they have lost the parent record. To automatically prevent this from happening we can use a convenient trick called ON DELETE CASCADE when we define the database

Example.

CREATE TABLE Orders
(
orderId INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25)
);

CREATE TABLE OrdersLine
(
orderlineId INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT,
CONSTRAINT fk_order_id
FOREIGN KEY (orderId)
REFERENCES Orders (orderId)
ON DELETE CASCADE
);

What is a foreign key with Cascade DELETE in SQL Server?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete in SQL Server. 

It is also possible to configure this in the SQL Server Management Studio (SSMS) 2014. 

Go to the table with the FK (foreign key). Right-click on the table and select the Design option.
Right click on the foreign key column, look under Relationships... (A Pop-up Dialog appears, Choose the FK relationship you want to edit). Scroll down to where you can see INSERT and UPDATE Specification, and chose on DELETE CASCADE. Click close to close the Pop-up.
(REMEMBER TO PRESS THE SAVE BUTTON in the toolbar)

http://stackoverflow.com/questions/4994587/sql-server-2005-cascading-delete