473,397 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,397 software developers and data experts.

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 3357
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Sugapablo | last post by:
I'm wondering if anyone knows of any code out there that someone might be sharing to generate the printing dimensions required for printing bank checks or tax forms such as 1099s? What I'd like...
3
by: Dave | last post by:
Hello all, In general, the STL doesn't have to perform safety checks. But there are some exceptions, and I'm trying to find as many examples as I can. For example, vector::at() must perform...
1
by: Amit | last post by:
Greetings. I am looking through some of the examples in Stroustrup's book and he has certain examples of doing compile time checks for a variety of things. One of them being the following... ...
3
by: Nick Hodapp | last post by:
How can I supress runtime security checks in a mixed-mode C++ assembly? SuppressUnmanagedCodeSecurityAttribute apparently only works for P/Invoke and COM interop. I've got a somewhat chatty...
1
by: Anders K. Jacobsen [DK] | last post by:
Hi Im developing an ASP.NET CRUD application where i need to do some authorization checks on surden actions. Eg. some account have access to delete in a sudden datagrid and some have not. So I...
5
by: Brian Mitchell | last post by:
I have no idea what I am doing wrong, but I have a CheckedListBox (Bound to an IList) on a TabControl and everytime I switch tabs I lose my checks. Is there something I'm missing? If I look at the...
6
by: Greg Stark | last post by:
There's another poster complaining about referential integrity checks causing deadlocks. Unfortunately I've deleted the message so this response (and the archives aren't responding) isn't going to...
6
by: Nicola Mezzetti | last post by:
Greetings, I write to ask information about how to disable the preprocessor checks on macros when compiling with command line Borland C++ compiler. Waiting for a reply, i thank you all for the...
80
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.