Are foreign keys really necessary in a database design?

Question

As far as I know, foreign keys (FK) 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?

Solution 1

Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default.

Solution 2

Foreign keys can also help the programmer write less code using things like ON DELETE CASCADE. This means that if you have one table containing users and another containing orders or something, then deleting a user could automatically delete all orders that point to that user.

Solution 3

I can't imagine designing a database without foreign keys. Without them, eventually you are bound to make a mistake and corrupt the integrity of your data.

They are not required, strictly speaking, but the benefits are huge.

I'm fairly certain that FogBugz does not have foreign key constraints in the database. I would be interested to hear how the Fog Creek Software team structures their code to guarantee that they will never introduce an inconsistency.

Solution 4

A database schema without FK constraints is like driving without a seat belt.

One day, you'll regret it. Not spending that little extra time on the design fundamentals and data integrity is a sure fire way of assuring headaches later.

Would you accept code in your application that was that sloppy? That directly accessed the member objects and modified the data structures directly.

Why do you think this has been made hard and even unacceptable within modern languages?

Solution 5

Yes.

1. They keep you honest
2. They keep new developers honest
3. You can do ON DELETE CASCADE
4. They help you to generate nice diagrams that self explain the links between tables

View more solutions

Comments

[Kibbee]

@Greg Hewgill This could pontentially lead to a lot of problems. You should be very careful with thinks like DELETE CASCADE, as in many cases, you would want to keep the orders created by a user when deleting the user.

[Codewerks]

Although this should probably be handled in business logic layer. Deciding whether or not to keep related child records, is not quite the same as ensuring that no values violate foreign key relationships.

[Tony Andrews]

Joel: "So far we've never had a problem." So far, I've never driven into a lamp-post. But I still think it's a good idea to wear seat belts ;-)

[Omid]

May be you never have SEEN the problem, but may be it's there... The most of databases use a convention like id_xxx that is exactly the same that ixXXX

[B Bulfin]

I work on systems that don't use them. And I regret it regularly. I have seen more instances I can count of non-sensical data that would have been prevented by proper constraints.

[John Christensen]

And having been working with foreign keys on our current project for nearly six months, I totally agree with this comment.

[si618]

The other issue is auditing, if auditing is not done at the db level, cascading updates or deletes will invalidate your audit trail.

[jcollum]

@Joel: Naming conventions in place of enforcement of rules? Might as well do away with type while you're at it.

[jcollum]

Good point, it would be nice to join two tables with "ON [Relationship]" or some other keyword and let the db figure out what columns are involved. Seems pretty reasonable really.

[jcollum]

+1 for a good analogy between encapsulation and FK/PK relationships.

[Eric Z Beard]

@jcollum, I made that comment during the beta of Stack Overflow, when pretty much everybody here knew who Jeff and Joel were, and most were probably listening to the Podcast, so Fog Creek was on everybody's radar.

[Peter Wone]

Uniqueness constraints indicate high cardinality which is used by the optimiser in selecting a join mechanism.

[Robert]

If you need an index create one, this should not be a primary reason for FKs. (In fact in certain circumstances (More inserts than selects for example) maintaining a FK might be slower. )

[MusiGenesis]

@jcollum: some would say "And?" while others would say "WTF?" (I just did), but I guess there's more than one way to skin a tooth. Nice use of "avatar", by the way. :)

[Fantius]

@Codewerks: Business logic can be in the DB.

[Sam Saffron]

Eric: FogBugz uses a naming convention for foreign keys. For example ixBug is understood to be an index into the primary key of the table Bug. So far we've never had a problem. -- Joel Spolsky

[Chad N B]

"So far we've never had a problem." -- Correction: You've never had a problem YOU KNOW OF--which is another great reason to let your tools help you.

[Peter Wone]

To respond to Eric's actual question, my understanding is that Fog Creek software is hosted on servers controlled by Fog Creek, not shrink-wrap software released into the wild. This means they can ensure that their database is manipulated only via their applications software. In this context I surmise that there is an object model that enforces constraints.

[gloomy.penguin]

that link is actually extremely fascinating... i'd truly like to know more details and i'm somewhat scared to use ebay now. for other people: click on the 4th question to see what he says about their db structure. the whole interview is worth watching, though. also... unibrow

[Fabricio Araujo]

It's like an onion. FKs are the last layer of defense. Unless it's an embedded local database, apps trying to do referential integrity is always an bad idea.

[Agile Jedi]

That's a horrible answer FKs genaerally can add extra overhead not improve performance.

[dspacejs]

what do you mean by honesty?

[user420667]

In SQL-Server, they are not indexed by default on either the referee or the referrer. sqlskills.com/blogs/kimberly/…

[Oreste Viron]

Honest with the conception I guess. It prevent you from cheating with the data by doing quick and lame programming.

[Littlefoot]

Nor in Oracle; you have to create indexes (on the FK columns) yourself.

[TomSawyer]

many forum DBs don't use foreign key like xenforo, phpbb... or even wordpress. using fk causes performance issues. many of them choose to handle orphan rows manually.

[Alex from Jitbit]

The benefit is performance. I'm not saying you should not have FK's, just strictly answering your question. Suppose you have a huge (100GB) table with a FK to another table. If you delete a record from "another table" - the engine will scan the entire 100GB table to make sure you're not deleting anything useful. Unless you have that FK column indexed (FK are not indexed by default in SQL Server)

[Tundey]

I'm not a db expert but I don't think that should be how you address performance problems. Like you said, you can index the FK column (which you'll realize pretty quickly that SQL doesn't do by default) and you do want the database to enforce that the record you're deleting isn't in use in your 100GB table.

[Alex from Jitbit]

I (mostly) agree. Just wanted to mention that when you're managing databases of size of tens of terabytes, dropping FKs is an an unspoken common practice among DBAs. In essence, at this scale you're moving to "NoSQL land", where you have to drop one of the "A", "C", "I" or "D" out of the "ACID" principle.

[philipxy]

@jumping_monkey Your edit was not a clarification of what the author said, it added something new, which is inappropriate. I rolled it back. It should be a comment suggestion to the author. Also it was not grammatically correct & it left out a space & it had unnecessary boldface, it was a bad edit for that content. Help center

[jumping_monkey]

Hey @philipxy, thanks for checking that. I would argue that it is, as referential integrity(which is the whole point of foreign keys) is a subset of data integrity. That's the reason i added it, with the link to read more about it. Anyway, you can remove it, your call. I like Guy's answer, that's what is more important. Cheers.