I have been using the following pattern to validate FOREIGN KEY (FK) parameters for stored procedures which execute INSERTs:
CREATE PROC spCreateChildFoo @ForeignKey_Id int, @Attribute varchar(50) AS IF NOT EXISTS (SELECT ForeignKey_Id FROM ForeignTable WHERE ForeignKey_Id = @ForeignKey_Id) BEGIN RAISERROR ('The input ForeignKey_Id does not exist', 16, 1) RETURN; END INSERT INTO ChildTable (ForeignKey_Id, Attribute) VALUES (@ForeignKey_Id, @Attribute)
I did this because I wanted to catch bad FK entries early in the execution of the stored procedure before any data is INSERTed, especially for procs that carry out multiple INSERTs. I also thought it would be a good way to return accurate error messages to ease debugging. This pattern works well at serving those two goals.
Is this a waste of time?
More specifically, should I just rely on FK constraints to validate the FK parameters and rollback if it fails?
Is it ultimately going to hurt the performance of my application as the database grows?