Best practices for stored procedures? Should they check for foreign keys before inserting?

Question

When creating a stored procedure that is inserted into a simple reference table, should I check if the FK exists and gracefully return an error or just let SQL throw an exception?

• What is the best practice?
• Which is most effective?

Just in case, someone does not understand my question:

• Table A
• Table B
• Table AB

Should I do:

 IF EXISTS(SELECT 1 FROM A WHERE Id = @A) AND EXISTS(SELECT 1 FROM B WHERE Id = @B) BEGIN INSERT AB (AId,BId) VALUES (@A, @B) END ELSE --handle gracefully, return error code or something 

or

 INSERT AB (AId,BId) VALUES (@A, @B) 

and let SQL throw an exception

thanks

Answer 1

If the tables are under your control, there is no need to perform additional validation. Just suppose they are configured correctly and let SQL handle any error. Constantly checking that you really did what you intended to do is overly protective programming that adds unnecessary complexity to your code.

For example, you will not write code like this:

 i = 1; if (i != 1) { print "Error: i is not 1!"; } 

And I see this situation as similar.

If the tables are not under your control, it may be useful to handle the error gracefully. For example, if this procedure can be performed on an arbitrary set of tables created by the user, or if it will be distributed to external users who need to set up tables in their own database, you might want to add some custom error handling. The purpose of this would be to give the user a clearer description of what went wrong.

Answer 2

As a basic concept, validating values ​​to potential error-enhancing code is good. However, in this case, there can be (at least theoretically) a change in table a or table b between existing checks and the insert statement, which would lead to a violation error fk.

I would do something like this:

 BEGIN TRY INSERT AB (AId,BId) VALUES (@A, @B) SELECT NULL As ErrorMessage END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage END CATCH 

The ERROR_MESSAGE() function returns an error that was processed in the try block.

Then in the executable code, you can simply check if the returned error message is null. If so, you know that the insert was successful. If not, you can handle this exception as you see fit.

Answer 3

I usually check Fk. If Fk does not exist, then output an error message and the insert statement will not be executed, the database will also not lock the table.