Board index » delphi » My Boss Hates Foreign Keys

My Boss Hates Foreign Keys


2003-10-28 11:45:16 AM
delphi24
Hi,
I am writing to this group to get your opinions about using foreign keys
with SQL Server. The company I work for handles insurance claims for
several hundred thousand people, and our database is built around
Microsoft SQL Server 7 running with 6.5 compatibility turned on using a
two tier client server model.
My new boss hates foreign keys, and in reviewing some scripts I wrote the
other day he asked me not to create foreign keys.
I sent him an email asking for his reasons not to use foreign keys and he
replied that they slow down the database and make it difficult to delete
records. This goes along with his attitude of not placing any business
logic such as constraints or triggers on the database, his refusal to
allow fields to be nullable, and changing most every int column he finds
into a tinyint column. Now he is my boss and I will do as I am told, but I
am beginning to think he is a junior level programmer who somehow managed
to get hired as a manager.
For your information, the code my boss writes in the application tier
usually involves writing business objects (native code compiled) that
encapsulates data access. Maybe he is so entranced with OOP that he sees
any business constraint outside of his native code as being
unsophisticated?
The questions I have are:
What are your thoughts on foreign keys?
Are there any valid arguments for not using foreign keys?
Do foreign keys really "slow down" selects, inserts, and updates?
 
 

Re:My Boss Hates Foreign Keys

Quote
The questions I have are:

What are your thoughts on foreign keys?
I use full PK/FK relationships and database constraints (not null,
validation triggers, etc) everywhere. I want the data in the database to be
perfect. If a bug slips by the middle-tier and tries to add a sales order
line item to a non-existent sales order, I want the database to error out.
It's the only way to have a balancable transaction system.
Quote
Are there any valid arguments for not using foreign keys?
Sure, speed takes a hit, but I'd rather sacrifice a few percentage
points of performance for data integrity.
Quote
Do foreign keys really "slow down" selects, inserts, and updates?
Sure they do, but with decent hardware, the speed difference should be
neglegable.
P.S. The reason "because it makes deletes harder" is complete B.S. Why in
the !@#% should the database allow a person to be deleted when an open
invoice is still attached to them? You are just asking for your accounting
department to set fire to your cubicle.
Eric
 

Re:My Boss Hates Foreign Keys

Quote
>make it difficult to delete records.
It shouldn't be so difficult to check wether data exists that shouldn't be
there (claims without a person etc.). If those exists let him explain this
to you.
hth,
Martin
 

Re:My Boss Hates Foreign Keys

On Tue, 28 Oct 2003 15:14:28 -0800, Craig van Nieuwkerk writes:
Quote
He has got some pretty good points. I am not sure if SQLServer has any
performance hit with foreign keys but some databases do as compared to
just having and index on the column. Interbase for example
(community.borland.com/article/0%2C1410%2C27569%2C00.html)

He is right that in a *pure* multitier system the database is really
just a data store and business logic should be in the middle tier. I
often still put some business logic in SP's and Triggers because it is
often possible to get greatly enhanced performance this way. I generally
handle deletion logic in the middle tier as well rather than the
database. If someone wishes to delete a record that would have a foreign
key then they are required to supply a replacement value or it cant be
deleted. This is a much better solution I think than a foreign key that
just pops up and error and scares the user into calling me (bad!). I
have seen many ligitimate examples of people wanting to delete records
with a foreign key.

In summary though, I think there is no right or wrong, it just depends
on what suits you and the project while still giving good performance.
You will probably get much different answers if you post this to the OOP
newsgroup than the SQLServers one.
But doesn't it make sense to have a child table reference its parent with
a foreign key so that the relationship of the tables is obvious and that
the business rule is enforced in a way that cannot be broken?
For example an orders table might have a foreign key to a customer which
prevents a order from existing without a valid customer (or prevents a
customer from being deleted while he has an order).
Also, wouldn't it make sense to create a constraint such that a table
might only allow one entry of a particular value, or one entry of a
particular value per some other field in the same table?
Example script:
alter table hospital_staff
add constraint unique (hospital_id, practitioner_id);
A practitioner can only be staff member at the same hospital once.
I have found it is quite good to place simple rules like these in a
central location (the database) so that:
A) The relationship between tables and field can be understood by looking
at the database schema
and
B) User and programmers can not make simple mistake that can wreck the
database
Last week, a few days after my boss requested no foreign keys, a
programmer mistakenly ran a query that dropped a set of contracts from our
database (a huge deal, as the contracts table is a very central table).
The programer had highlighted the wrong like in the query window and ran
excute. If there were foreign keys in the database (our database has
hundreds of tables), the execution of the statement would have failed, as
the contracts would be referenced by many claims and insurance coverage
records, but because there are no foreign keys the database happilt lets
you delete these records.
Anyhow, like I said this is a two tier system, and even in a three teir
system I'd still like to see nullable fields, constraints, and fields
in one table that refer to fields in another, if it were up to me :p
 

Re:My Boss Hates Foreign Keys

On Mon, 27 Oct 2003 23:37:56 -0600, Eric Hill writes:
Quote
P.S. The reason "because it makes deletes harder" is complete B.S. Why
in the !@#% should the database allow a person to be deleted when an
open invoice is still attached to them? You are just asking for your
accounting department to set fire to your cubicle.

Eric
Thanks Eric, my point exactly. Currently our database has almost no
foreign keys and well over half a million records. There are all sorts of
records that reference data which no longer exists. There is billing
information about people that don't exist, and doctors that work at
hospitals that been vaporized. All bogus data because someone made a
programming mistake, or ran a delete query by accident, or perhaps he
purposefully deleted the data without actually understanding the
ramifications.
If I were to delete a person from our database I would like to know if he has
outstanding claims to be paid.
With foreign keys I would know right away that the delete is unsafe becase the
database would tell me when I tried to delete.
Generally I don't even like to delete. Instead I prefer to have some
active flag in a record and switch it to false, eliminating that record
from the users screens, but still being stored in the database.
I guess we see things pretty much the same. It irks me that my boss thinks
the way he does :p
 

Re:My Boss Hates Foreign Keys

On Tue, 28 Oct 2003 06:38:57 +0100, Martin Brekhof writes:
Quote
>>make it difficult to delete records.
It shouldn't be so difficult to check wether data exists that shouldn't
be there (claims without a person etc.). If those exists let him explain
this to you.

hth,
Martin
Yeah, there definitely are a lot of orphaned records in our database. He
thinks he is a smart enough of a programmer to enforce all the busines
rules in objects coded in Delphi that apply and retrieve the data (no data
aware controls in his screens).
The problem I see with this is that its too easy to make a mistake. You
are relying on your memory to write the proper code for your objects and
the data contraints they enforce, especially when a table is access from
many directions.
Anyhow, yeah you are right, the database has lots of bogus data already.
 

Re:My Boss Hates Foreign Keys

On Tue, 28 Oct 2003 16:46:19 -0800, Craig van Nieuwkerk writes:
Quote
If you want it then I see no problem in having constraints in the DB as a
backup in case the logic in your middle tier fails. I just wouldn't only put
the logic in the database. In a two tier client/server system though, there
is more arguement to put business rules in the database.

Craig.
Certainly I'd never try and write a datbase system where all of the
logic resides in stored procedures, triggers, and constraints on the
database.
All I'd like to see is tables refence other tables, and maybe some
constraints.
In a three tier model I undersatnd the middle tier handles logic, and
there is a lot of logic I'd not want, or would see fit, to be handled
by the database.
Basically what i want to prevent is data from being corrupted on the
server so that it is difficult for even the programmer to make mistakes.
Like I metioned before, customers shouldn't be deletable if they have
attacjed claims, or the same employee can not be listed in a deparment
twice, ect. and I see the datbase as the place to enforce this
logic making things much more difficult to break by whay of programmer
error or accidental sql script error.
 

Re:My Boss Hates Foreign Keys

coderx <XXXX@XXXXX.COM>writes
Quote
Hi,

I am writing to this group to get your opinions about using foreign keys
with SQL Server. The company I work for handles insurance claims for
Hi,
I don't know your bosses background but on systems with TERABYTES of data on
large machines running 90% or more of capacity, you just do not add anything
like this that will slow the system down. Life is a bit different with,
relatively, small SQL Server databases on, relatively, powerful servers. (I
am talking about top-end AS/400 systems running interactive Telecom
software). He does have a point in some cases
My real view ... it depends. Sorry.
David
 

Re:My Boss Hates Foreign Keys

Quote
My new boss hates foreign keys, and in reviewing some scripts I wrote the
other day he asked me not to create foreign keys.
Your boss should read a book.
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
www.upscene.com
 

Re:My Boss Hates Foreign Keys

Quote
My new boss hates foreign keys, and in reviewing some scripts I wrote the
other day he asked me not to create foreign keys.
What are your thoughts on foreign keys?
Basics - that is Database Design 101 - you need to get your boss to
read the "Database Design for Mere Mortals" book (by Hernandez -
excellent read).
Quote
Do foreign keys really "slow down" selects, inserts, and updates?
No, not really - but on the plus side, they help you enforce database
integrity, which is what relational database design is all about in
the first place.
Your boss must be a database "self made man" without any deeper
knowledge - not using foreign keys is like driving at night without
headlights - no reasonable man would do that.......
Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch
 

Re:My Boss Hates Foreign Keys

Quote
He is right that in a *pure* multitier system the database is really just a
data store and business logic should be in the middle tier.
Even then - you do WANT to have some checks, constraints etc. on your
database - who knows how some folks manage to get access to your
database, and if you enforce at least basic rules on the database
itself, no matter where they come from (e.g. opening a table in Excel
or what have you), they'll still have to comply at least with some
rules.
If you leave your database fully open - no constraints, no checks, no
relationships - you're just asking for trouble.
Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch
 

Re:My Boss Hates Foreign Keys

Foreign keys are indexes and indexes slow down updates, but they greatly
speed up searches and joins. If you have standard indexes instead of foreign
keys, you gain nothing performance wise, but lose integrity checking. If you
don't have indexes, your database model is useless.
If you don't have business logic in database, you must have it in
application which adds a lot of additional querying (=network traffic). This
has some sense in 3 tier, where middle tier implements the business logic,
but is located either on the same machine as database server or very near,
so has a fast connection with it. In 2 tier this is plainly silly.
Also, if someone bypasses your application, he (she) has totally unprotected
database.
--
Robert Cerny
DelphiShaman
"coderx" <XXXX@XXXXX.COM>writes
Quote
Hi,

I am writing to this group to get your opinions about using foreign keys
with SQL Server. The company I work for handles insurance claims for
several hundred thousand people, and our database is built around
Microsoft SQL Server 7 running with 6.5 compatibility turned on using a
two tier client server model.

My new boss hates foreign keys, and in reviewing some scripts I wrote the
other day he asked me not to create foreign keys.

I sent him an email asking for his reasons not to use foreign keys and he
replied that they slow down the database and make it difficult to delete
records. This goes along with his attitude of not placing any business
logic such as constraints or triggers on the database, his refusal to
allow fields to be nullable, and changing most every int column he finds
into a tinyint column. Now he is my boss and I will do as I am told, but I
am beginning to think he is a junior level programmer who somehow managed
to get hired as a manager.

For your information, the code my boss writes in the application tier
usually involves writing business objects (native code compiled) that
encapsulates data access. Maybe he is so entranced with OOP that he sees
any business constraint outside of his native code as being
unsophisticated?

The questions I have are:

What are your thoughts on foreign keys?

Are there any valid arguments for not using foreign keys?

Do foreign keys really "slow down" selects, inserts, and updates?
 

Re:My Boss Hates Foreign Keys

"coderx" <XXXX@XXXXX.COM>writes
Quote
What are your thoughts on foreign keys?

Are there any valid arguments for not using foreign keys?
Do foreign keys really "slow down" selects, inserts, and updates?
Foreign keys aren't called "constraints" for the fun of it. They are there
to protect the integrity of your data, and stop you from doing things that
you shouldn't be doing (like deleting customers who still owe you money!).
Do you want duff data in the database ? If so, then don't bother with
foreign keys. Developers will make mistakes (and already have by the sounds
of it) - database constraints are your last line of defence to prevent dodgy
data in your database.There may be some minimal impact on performance due to
adding f.keys but you get greatly improved data integrity. What is the cost
of not having foreign keys - how much is it costing you to sort out bad data
?
Andy Mackie.
 

Re:My Boss Hates Foreign Keys

"coderx" wrote
Quote
the code my boss writes in the application tier
usually involves writing business objects (native code compiled)
that encapsulates data access.
So do I. That doesn't account for his views.
Quote
What are your thoughts on foreign keys?
Let's try a basic reference:
"Primary and foreign keys are the most basic components of relational
theory, upon which data models are constructed. ... Each relationship in a
model must be supported by a foreign key."
--page 162, The Data Modeling Handbook : A Best-Practice Approach to
Building Quality Data Models.
Michael C. Reingruber, William W. Gregory.
Quote
Are there any valid arguments for not using foreign keys?
Existing applications that ignore relational data integrity?
Quote
Do foreign keys really "slow down" selects, inserts, and updates?
Inserts and updates, yes. So can indexes (indexes must be maintained). So
does putting data in your tables. Getting garbage slightly quicker is not a
good optimization.
Bottom line: I am an OO programmer who believes strongly in business logic
being modeled in code and executed in the business layer. Nevertheless,
working without a net against a '{*word*192}' database that cannot enforce basic
relational integrity constraints is both unnecessary and ill-advised.
Personal aside: life's too short to work for a boss you don't respect.
bobD
 

Re:My Boss Hates Foreign Keys

Quote
The questions I have are:

What are your thoughts on foreign keys?
Foreign keys make your data valid despite the application used to modify
the data. Ask you boss if he can guaratee you that every modification to
the data will be made through the middle tier where he puts his logic.
If he can not say that (which he won't be able to) then explain that is
why you need foreign keys to make sure that your database integrity is
solid apart from the application.
Quote

Are there any valid arguments for not using foreign keys?
Not really, IMO.

Do foreign keys really "slow down" selects, inserts, and updates?
If you index properly and design the FKs well it should actually help
performance, especially if you are currently doing a lot of joins.
HTH,
Ross