Hi everyone!
Got a question about foreign keys. As for me, I think that they are really convenient thing. But I've read some opinions that they add overhead and it's not recommended to use them on a highload projects. I googled a bit about this topic, but found nothing.

What do you think? Is this true?

you've been reading the wrong stuff

the "overhead" of foreign keys is inconsequential

the benefit of foreign keys is data integrity

you decide

The overhead is performance of inserts/updates and delete operations on the tables. Every insert needs to cross check with the other tables that the key exists. So for a table with only 1 foreign key the insert will cause a check in the foreign key table before applying the INSERT statement.

For large numbers of inserts, deletes and updates this will become an issue, but, for OLTP type transactions where a single insert it taking place then indeed the overhead will not be significant and would justify including the foreign key constraints.

On warehouse type databases where large volumes are loaded periodically, then using foreign key constraints can be a hindrance due to the performance overhead. A better approach here is to load the data into staging tables (identical setup to the primary table) and load the information in here. None of the tables will have foreign key constraints. However, if you generate an SQL statement that verifies the integrity of the data will a) verify the integrity of the data and b) will allow data cleanup before applying into the primary table. It should be noted that warehouse type databases perform inserts and rarely perform updates or deletes so the data remains static.

Hope this helps?

Rudy, Ronan, thanks for answers! Now the benefits and limitations of foreign keys are obvious to me