469,090 Members | 1,101 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

FK Checks

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?

On one hand, if you check yourself and the key does not exist you can
gracefully handle it (maybe exit out of method with error). If you let
SQL do it, the server will throw an error which cannot be suppressed.

On the performance side, you doing the check will incur a slight (VERY
slight) hit since SQL will ALSO check anyways.
Jul 20 '05 #1
3 3249
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
Jul 20 '05 #2
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
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.)


I should have been more explicit in my question. I was referring to
performing checks within stored procedures.

Assuming Table2 has a FK relationship with Table1.

IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @field1)
INSERT INTO Table2 (@field1, @field2, field3)

as opposed to

INSERT INTO Table2 (@field1, @field2, field3)
IF @@Error RAISERROR('Unable to insert into Table2 because of missing
constraint', 15, 1)
Jul 20 '05 #3
Jason (Ja*******@hotmail.com) writes:
I should have been more explicit in my question. I was referring to
performing checks within stored procedures.

Assuming Table2 has a FK relationship with Table1.

IF EXISTS (SELECT * FROM Table1 WHERE Table1.Field1 = @field1)
INSERT INTO Table2 (@field1, @field2, field3)

as opposed to

INSERT INTO Table2 (@field1, @field2, field3)
IF @@Error RAISERROR('Unable to insert into Table2 because of missing
constraint', 15, 1)


Again it depends. Say that your procedure is to be called from a GUI. In
this case you can presume that the GUI gets the information from the
databaes, and a foreign-key violation could only occur if the GUI is
incorrect. On the other hand, say that you are accepting data from an
external source over which you have no control. In this case, it is better
to check explicitly, so that you can log incorrect data appropriately.
Recall that when a constraint blows up, you cannot in T-SQL determine
which constraint that fired.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Dave | last post: by
1 post views Thread by Amit | last post: by
3 posts views Thread by Nick Hodapp | last post: by
1 post views Thread by Anders K. Jacobsen [DK] | last post: by
5 posts views Thread by Brian Mitchell | last post: by
6 posts views Thread by Nicola Mezzetti | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.