Recently I asked a question in StackOverFlow:
Are foreign keys really necessary in a database design?
As far as I know, foreign keys are used to aid the programmer to manipulate data in the correct way. Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys?
Are there any other uses for foreign keys? Am I missing something here?
Many people responded and there were all kinds of opinions. The strange thing I noticed was that many people including Joel Spolsky do not use foreign key constraints in their databases.
You can build a complete application without even using a single foreign key constraint. Foreign keys, as a matter of fact are not essential in your databases, but they help a lot in programming and making robust systems. Hence the use of foreign key integrity constraints is strongly recommended.
These are the obvious advantages of using foreign key constraints:
- They help in keeping data integrity by removing the possibility of unwanted data piling up and cluttering the database. Mostly this data will not affect your business logic, but eventually you will have to take up the task of cleaning up the mess manually.
- They help in visualizing and preparing database diagrams. Many tools are available which will automate this task.
- They may give performance benefits. In systems which auto index fields, foreign key references can give a performance boost.
- Foreign keys can also help the programmer write less code using things like ON DELETE CASCADE and other referential actions.
If you know of any other advantages please add them below.
Curtis Poe has written a very good article about this mentioning the fundamental flaw in the thought against foreign keys:
If you have an SQL statement inserting data, you want the receiving table to have the data validation in the form of foreign keys, custom data types, enums, triggers and so on. If you’re writing code for personal use or you control an open-source project, skip this if you want. It’s your code; it’s your choice. However, if you’re writing code for a business, you don’t know who will be maintaining it tomorrow. Maybe they don’t know about your clever data validation routines which allow you to forego foreign keys. Maybe they’re finding your code too slow so they decide to write directly to the database themselves. Whatever the reason, by putting the data validation as close as possible to the data munging, you make it harder to circumvent.
In effect, foreign key constraints are a way of implementing a set of guidelines for proper querying and manipulation of data in a database. You can do away with them, but you are bound to make mistakes eventually without them.
Hey,
There are new generation databases like Teradata, Netezza etc which do not even implement the conventional concepts of primary keys…. And these databases work much faster than the conventional ones. But i dont knw y….
How can they work without primary keys? I mean the database system should support the concepts. Right?
So what should a developer do to get the functionality of a primary key (like non-duplication)? Should he program it himself?
@Markus
(used for example in Amsterdam by bike deliveries)
There are certainly bikes without brakes
http://www.oldskooltrack.com/files/nobrakesok.frame.html
Martijn,
Amazing !!!
Your database is required to
(a) store data
(b) be circumspect of the data passed to it and run its own validations
(c) implement application / business logic
In case of (b) or (c) – not having fkeys is not an option. In case of (a) only, it is an option
Your database will be used by
(a) One application
(b) More than one applications
Since it is much harder to ensure that all validations are conducted equally effectively across applications, it is preferable to do as many validations in the db layer. Why just restrict yourself to fkeys, you might want to implement many more validations using stored procedures too in case of (b) !
You have faith in your current and future development teams to rigorously architect and build application tiers which can conduct all relationship validations roughly as effectively as it could get done in the db layer
(a) true
(b) false
If it is (b) use foreign keys.
You use OOP / class hierarchies, and probably ORM as well and your database implements class inheritances using
(a) one table per class hierarchy approach
(b) one table per leaf class approach
(c) one table per class approach
If you are using option (b) above it is extremely difficult to use foreign keys, since the foreign keys will refer to different tables based on the leaf class (or the discriminator value) .. stay away from fkeys in this case.
There is a huge example of a database design that doesn’t make use of foreign keys: SAP.
All of SAP Solutions uses SAP NW as foundation of all Business Solutions.
SAP NW (SAP Netweaver) implements a database abstraction layer to enable Database Vendor independent platform, enabling install SAP Servers in many kinds of database server: Oracle, DB2, MSSQL, MaxDB.
In this implementation SAP designs his own database layer, with your rules and features.
SAP enables Primary Key, Index, Unique Index Keys as transparent use of Primary Key, Index, Unique Index Keys features of the Vendor Databases. But doesn’t make use of Foreign Key constraints in your Database Abstraction Layer and the Vendor Database.
With the use of database without Foreign Keys, enables SAP a more flexible use of database tables and obligates SAP Systems to control his own the relation and integrity related to the relation of database fields, is a great responsibility but the inconsistencies are not so prejudice to the system.
The results of this database inconsistencies are waste of database storage and obligation of managing records without his “father” registry.
SAP implementation is a result of a view of pragmatism, enabling a huge software requirement (Platform Independency) over strict rules of database design.
[]‘s.
I still remember the days when I learned database design in school, we first did it theoretically and later on did practical examples with Access and MySQL. We didn’t set up a InnoDB database for MySQL and always used the default MyISAM engine, so that the default installation didn’t have foreign key checks.
There’s basically one thing you need to make sure of when not using foreign key’s: the fk id must be a valid id e.g. it must exist in the database and not point to a non-existing record. It’s also very easy to delete an record where a foreign key points to, so you need to check beforehand if there are any foreign keys that point to the record you want to delete, otherwise there will be a lot of inconsistencies.
I now use InnoDB (when using MySQL) or PostgreSQL most of the time and it definitely helps to ease the development.
Cascading updates/deletes are also a nice thing, but you need make sure you understand the whole impact. Deleting a user record might also delete some entries, which might delete some comments, which in turn deletes some other stuff.
One thing I noticed with MyISAM is that it’s a really fast database engine because it’s pretty feature-less.
If you want the RDBMS to handle cascade delete and other referential integrity, yes.
Dhananjay, Antelio, Arthur,
Thanks for reading and taking time to provide detailed opinions.
I design databases on small projects. I am typically the sole designer for a team of about 15-20 developers and systems typically have 150-300 tables.
Unless I am the only person who is going to write code against a database, I will not design a database without foreign keys (or some equivalent enforcement).
The reason:
When you design the database (or sections of an existing database) in advance of the application developers use of it, foreign keys give guidance to the developers. Just in case they don’t completely understand the structure (common), foreign keys kick out errors as they do things incorrectly. Then they change what they are doing or they come and ask me questions and I explain it to them.
To develop a system without FKs (or some enforcement), every person who develops against the system has to have a perfect understanding of the relationships – way too much to ask.
Two other notes:
1. ORMs often use the database FK metadata them to create the correct XML that includes relationships.
2. Even if your developers have perfect understanding of the system – what about the next contract that will attach to the database? I see problems without FK constraints.
A different Bob,
True. Thanks for writing.
These are all great points. Here’s what I wonder; why was the latest and greatest, high-performance database engine (Falcon) created without fk support?
I don’t understand it at all!
Thoughts?
Hi there,
I saw your post at StackOverFlow and replied there but no response yet. Anyway, I understand foreign keys may result in lockings and my question is, will this be a “performance” impact for an online game with high reads/writes?
Cheers,
darnpunk
Two things are being confused here:
1. Relationship between two tables
2. How to ensure the relationship
#2 is where the choice of Foreign Key comes in (#1 is an aspect of your data model). You can choose to define/enforce the relationship through your own code, OR you can use Foreign Key.
Doing it yourself gives you some benefits like optimization/customization and non-benefits like someone less knowledgeable overriding your code.
Using Foreign Key has the benefit of being automatically enforced/managed by the database. Disadvantage is that it might not be optimized, and can slow down database updates (since each update will be verified by the database).
Conclusion – If your database is constantly updated, maybe it is better to use custom code instead of foreign keys. Otherwise, maybe best to use foreign keys.
[...] Are foreign keys really necessary in a database design? [...]
This is ossem sight
Please sent me difference between Primery & Secondry keys.
Somebody mentioned Falcon is great, one reason might be this is not having Foreign keys, then how is the data integrity taken care. Is it completely moved to other layers
Johnson can answer this ..
[...] Are foreign keys really necessary in a database design? [...]
CONSTRAINT FOR FOREIGN KEY……………..
>You can add if current feild values are NULL or exist in the referenced table.
>you can delete a FORIEGN KEY constraint.
INTRODUCTION OF RDBMS…….
DBMS allow you to create and maintain database. In the unit, we will discuss about the various data models and architecture in DBMS. We will discuss RDBMS which is used to store data in the form of related tables. In addtion, we will discuss condidate key, primary key and foriegn key in DBMS.