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
- It maintains referential integrity (yes but can be maintained without it too)
- Easier Detective work (of course)
- 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
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?