Saturation of Foreign Keys?

Question

Saturation of Foreign Keys?

Asked 8 years, 2 months ago

Viewed 118 times

2

After a long time with non-relational database I decided to come back and I came across a strange situation. My app is a simple chat where this chat has sub rooms.

I did a little demonstration:

I need the user id in the room to know which chat room it is in and also need the user in the message to know who sent it.

I’m almost sure this is wrong, is it really necessary these Foreign Keys? or could remove them leaving only the field controlled by the application?

@Edit

Ignore modeling errors, was made only for demonstration.

1

Fks are optional, you use if you want to leave the bank the guarantee of relational integrity; but if there’s no reason not to create Fks, the default is to create them (they don’t usually get in the way of anything). Now, does your modeling look weird: user id in the room? There’s only one user in each room??

– Caffé

2016/05/03 at 13:44
True... there was an error there, I edit warning of the error. With all these FK’s the oracle data Modeler becomes a mess of the impression that is wrong by that of the question. Thank you for the answer.

– Lucas Bertollo

2016/05/03 at 14:15
Okay, I extended the comment in one reply.

– Caffé

2016/05/03 at 14:37

1 answer

Browser other questions tagged sql database oracle

You are not signed in. Login or sign up in order to post.

by Caffé • **17,429** points · Answer 1 · 2016-05-03T14:37:31+00:00

Foreign Keys in the database are optional, you create them if you want to leave with the database server the guarantee of relational integrity; for example: the bank will not let create a record with a Foreign key pointing to a non-existent record, or deleting a record for which there are other records related via FK.

When the database is used by a single application and the application itself will take care of these validations, the Fks in the bank can be waived.

One has to consider the cost of doing these relational integrity checks in the application or let the bank do them - probably the bank will do it with much more performance because in addition to being an expert on the subject, it is closer to the data and has features that go beyond simple SQL queries to do it.

Therefore, if there is no reason not to create Fks, the default is to create them; after all the guarantee of relational integrity is one of the great proposals of relational database servers.

As to the saturation of Foreign Keys, it is necessary to observe first if the modeling is correct, but it can also happen that it is correct and the excess of Fks cause some problems.

There are database servers (Dbms) that have Fks limits for a table, Among them there are those that block the creation of a new FK for an already saturated table and there are others that allow creation but then may present problems when trying to delete or change records for which there are many Fks. This can be a problem in systems with thousands of tables and tables with many columns.

To avoid these types of problems in large databases, it is necessary to group the information in smaller tables instead of drawing gigantic tables.

Update: Anyway Dbms are always improving and decreasing their limitations.

For example, the SQL Server 2012 had a recommended limit of 253 Fks. I’ve seen tables with over 300 FK incoming running on SQL Server 2005 but it was impossible to modify or delete records on some servers. Currently the documentation claims to support 253 Fks Outgoing and 10,000 incoming!.

_{I could not find documentation of the limit of FK per table in Oracle.}