Jason (Ja*******@hotmail.com) writes:
Suppose you have two (or more) tables with foreign key constraints. My
question is thus:
Is it better to check if the fk exists before you try to perform the
insert or let SQL do it for you?
Depends on business requirements. Basically, Fkeys is the database's
mean of protection against bad data. If the user interface does not
perform any checks itself, and relies on the database, you may avoid
integrity violations, but the users may not get adequate error message.
The way I see database constraints constitutes an inner defense line.
The user interface should help the user, and have its own defense line.
Error messages from the database exposed to the user, should be considered
a bug.
But validation in a GUI, can be quite different from the validation in
SQL Server. Normally you don't let the user to type in the FK as free-
text, but you let him choose from a drop-down box or from a search screen.
Once you know, the user has selected data this way, you can assume that
you are safe. (Although, someone may delete the row before the users
saves.)
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp