Sql – Indirect Foreign Key

by

You should always enforce referential integrity by using “normal” FOREIGN KEYs.

In a nutshell, FOREIGN KEYs have the following advantages:

  1. They are already implemented within the DBMS.
  2. They are declarative, self-documenting and “obvious”.
  3. They cannot be bypassed (unless explicitly disabled or dropped).
  4. They are correct.
  5. They are fast.
  6. They support cascading referential actions (such as ON DELETE CASCADE).
  7. The DBMS knows the data is related, allowing it to find a better query plan in some cases .
  8. If you are using an ORM tool, it can automatically generate references between objects.

And here are the corresponding disadvantages of enforcing referential integrity in the application code:

  1. You are duplicating the work that has already been done.
  2. It’s imperative, probably “buried” deep in your application source code, and harder to maintain.
  3. A single client application that has a bug can break the referential integrity (and corrupt the data).
  4. You are likely to implement them incorrectly in your application code. It looks simple from the outset, but in a concurrent environment, it is easy to introduce race conditions.
  5. Even if you have implemented them correctly, you probably used some form of locking to avoid race conditions, which is likely to be slower / less scalable than specially optimized FKs built into the DBMS.
  6. You have to implement cascading yourself.
  7. The DBMS doesn’t know the data is related, which may produce sub-optimal query plan.
  8. You may need to do more manual work in your ORM tool of choice.

Is there a name for this?

Not that I know of. I heard a term “generic FKs” being used, but that’s probably not universal.

Is it good practice?

No (see above).

Any performance considerations?

Yes (see above).

More Related Contents:

  1. How to design a product table for many kinds of product where each product has many parameters
  2. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  3. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  4. designing database to hold different metadata information
  5. Defining multiple foreign keys in one table to many tables
  6. Why use multiple columns as primary keys (composite primary key)
  7. Database schema which can support specialized properties
  8. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  9. What is Normalisation (or Normalization)? [closed]
  10. Schema for a multilanguage database
  11. Calendar Recurring/Repeating Events – Best Storage Method
  12. Same data from different entities in Database – Best Practice – Phone numbers example
  13. MySQL – Conditional Foreign Key Constraints
  14. NoSql vs Relational database
  15. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  16. How should I implement this schema in MongoDB?
  17. How do you deal with polymorphism in a database?
  18. Nullable Foreign Key bad practice?
  19. How do I design a database to store properties, selecting attributes by synonyms
  20. How should international geographical addresses be stored in a relational database?
  21. Still Confused About Identifying vs. Non-Identifying Relationships
  22. Can we create multicolumn unique indexes on MS access databases?
  23. (Database Design – products attributes): What is better option for product attribute database design? [closed]
  24. Composite Primary Keys : Good or Bad?
  25. Minimum no of tables that exists after decomposing relation R into 1NF?
  26. A column as primary key or two foreign keys as primary key
  27. How to Store Historical Data [closed]
  28. Relational Data Model for Double-Entry Accounting
  29. Is there a severe performance hit for using Foreign Keys in SQL Server?
  30. MySQL Relationships

Leave a Comment