Why should I use foreign keys in database?

Question

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my projects and I never felt the need. I did work with professional databases that had foreign keys constraints.

I am now at a position where we are building a new application/database and I am thinking should I use foreign keys or not? This is going to be a professional product. I will also consider implementing this in my existing projects if I get a satisfactory answer.

This article on why to use foreign keys exactly addresses my concerns. It main crux is

1. It maintains referential integrity (yes but can be maintained without it too)
2. Easier Detective work (of course)
3. Better Performance (I am not quite sure)

My question is, should I use foreign keys or can I live without them. What are strong pros and cons from a developer who worked in such scenarios.

---

Example: Now an important part of using foreign keys is added complexity that is added to design. For example, a simple delete may not work, or it may delete other records you are not aware of. Let's consider that scenario.

I have a database with user and user_comments tables.

create table user(
user_id int not null identity,
user_name varchar(50),
...
)

create table user_comment(
comment_id int not nul identity,
user_id int,
CONSTRAINT FK_USER_USERID FOREIGN KEY (user_id)     
    REFERENCES user (user_id)
    ON DELETE CASCADE    
    ON UPDATE CASCADE 
)

Here, if I delete a user, all his comments will automatically be deleted. I know I can change that behavior, but my question is, are Foreign keys worth using with their added complexity? What are pros and cons from SE.stackexchange users? Am I potentially looking at some horror stories? Can someone comment on how it improves performance?

Answer 1

1. It maintains referential integrity (yes but can be maintained without it too)

You are technically correct that if you're able to maintain referential integrity yourself, you don't need the constraint to exist. But by that same logic, you don't need fire insurance as long as your house doesn't burn down, and you don't need health insurance as long as you don't get sick.

While technically correct, the underlying assertion than you can do it all flawlessly is simply a failure to recognize the possibility of you (or any other developer) making a mistake.

Accidentally breaking referential integrity without foreign keys works without any issue. But later on, when you want to retrieve the data, it blows up in your face.

• Who set this data?
• When did they set it?
• Why did they set it to this value?

These question become very hard to answer.

Accidentally breaking referential integrity with foreign keys blows up in your face immediately.

• Who set this data? You did.
• When did they try to set it? Right now.
• Why did they set it to this value? Since you're doing it right now, you're logically the best source to know what it is you're trying to do.

Troubleshooting the issue becomes so much easier when you're at the source of the problem already.

2. Easier Detective work (of course)

I assume you mean the thing I just described.

3. Better Performance (I am not quite sure)

Can someone comment how how it improves performance?

Foreign keys don't improve performance, at least not directly. The performance gain is achieved by the use of indexes. It just so happens that PKs and FKs are automatically indexed because they are very frequently used for searching, making them prime targets for search optimization.

In here, if I delete a user, all his comments will automatically be deleted.

This is not inherent to a foreign key. This is inherent to setting ON DELETE CASCADE on the foreign key. Cascaded deletes are a nice-to-have feature but they are not the core use case of foreign keys. The core use case is maintaining referential integrity.

My question is should I use foreign keys or can I live without it. What are strong pros and cons from a developer who worked in such scenarios.

my question is, is Foreign keys worth using with its added complexity

I'm not seeing the complexity you're talking about.

If you claim to already be capable of handling referential integrity, that means that I should be able to sneakily put a FK on your FK-less column, and you would be unable to notice that I put an FK on your column. There is no complexity from having the FK.

Setting up the FK is trivial. Yes, it requires an explicit SQL command, but the command is very copy/pastable:

CONSTRAINT unique_name FOREIGN KEY fk_column_name REFERENCES pk_table (pk_column_name)

While the lazy developer in me does wonder if naming a constraint is really necessary, the other information you need to add is logically always required to set up a relation between two columns. Other than the name, it's about as simple as it can be.

The performance gain from having an index on the column is inherent to having an FK on the column. Setting an index without a FK is about as complex as setting a FK:

CREATE INDEX unique_name ON fk_table_name (fk_column_name)

So again, I'm not seeing the added complexity from actually using a foreign key.

Answer 2

If you delete a user, why would you possibly want to keep their now-orphaned comments around?

The central advantage of a relational data store is being able to guarantee that such anomalies never happen. If you like and want such guarantees, then it's a good idea to implement them at the lowest possible level, i.e. build them into the database engine, because this is faster and safer than doing it yourself.

If you don't want them, then you are better off with a non-relational data store. But using one model of engine and then refuse to take advantage of one of its major benefits seems oddly pointless.