Saturation of foreign keys?

Question

Saturation of foreign keys?

Navigation

#1 by (1 votes)

2

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

I made a small demonstration:

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

I'm pretty sure this is wrong, do I really need these foreign keys? or could you remove them by leaving only the field controlled by the application?

@edit

Ignore modeling errors, just for demonstration purposes.

sql oracle

asked by anonymous 03.05.2016 / 15:13

1 answer

Using jsPDF, are there ready templates? How to print / plot a table in #R?

score 1 · Accepted Answer

Foreign Keys in the database are optional, you create them if you want to leave the relational integrity guarantee with the database server; for example: the bank will not let you create a record with a foreign key pointing to a nonexistent record, nor will it delete a record for which there are other related records 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 database can be dispensed with.

You have to consider the cost of doing these relational integrity checks on the application or let the bank do them - probably the bank will do much more performance because it is closer to the subject, features that go beyond simple SQL queries to do so.

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

As for the foreign keys saturation , you should first see if the modeling is correct, but it can also happen to be correct and the excess FKs cause some problems.

There are database servers (DBMSs) that have FK limits for a table, among which there are those that block the creation of a new FK for an already saturated table, and there are others that allow creation, but can later 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, you have to group the information into smaller tables instead of drawing gigantic tables.

Update: In any case, DBMSs are always improving and decreasing their limitations.

For example, SQL Server 2012 had a recommended limit of 253 FKs . I have seen tables with more than 300 incoming FKs working in SQL Server 2005 but it was impossible to modify or delete records on some servers. The documentation currently claims support for 253 FKs and 10,000 incoming ! .

I did not find documentation of the FK limit per table in Oracle.