In my 10+ years of experience in the IT field, I have never used foreign keys in any of my project and I never felt the need. I did work with professional databases which had foreign keys constraints.
I am now at a position where we are building 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 it. 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. Lets consider that sencio in answer my question.
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 )
In here, if I delete a user, all his comments will automatically be deleted. I know I can change that behavior, but my question is, is Foreign keys worth using with its added complexity, What are pros and cons from SE.stackexchange users. Am I potentially looking at some horror stories? Can someone comment how how it improves performance?