SQL Server – Relying on Foreign Key Constraints for Error Handling

foreign keysql serverstored-procedures

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.

Questions

  • 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?

Best Answer

Yes, this is a waste of time at least when your stored procedure is just the INSERT statement that may cause the foreign key violation. Where a foreign key is in place, validation of any attempted FK value is made against the reference table by the database engine itself.

If the value does not exist in the reference table, you will both receive an exception and have your INSERT statement aborted.

So, this would be perfectly enough:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute);

On the other hand, if your procedure is more than a single INSERT statement and you would like to abort the entire procedure on an invalid insert at its beginning, then preliminarily checking the FK value may not be entirely pointless. However, valid FK values would still be causing double lookups, and that is neither good nor necessary. To achieve the same effect of aborting the entire procedure, you could just put your procedure's body into a TRY/CATCH block:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

BEGIN TRY
    INSERT INTO ChildTable (ForeignKey_Id, Attribute)
    VALUES (@ForeignKey_Id, @Attribute);

    ... -- other statements
END TRY
BEGIN CATCH
    RAISERROR ('The input ForeignKey_Id does not exist', 16, 1);
    -- or you could catch ERROR_NUMBER(), ERROR_MESSAGE() etc.
    -- and re-raise the original exception
END CATCH;

More info:

Related Question