Carousel Studio

Ben Dicken

@BenjDicken

Foreign keys vs constraints. Many conflate the two. Foreign key: A column that establishes a relationship between two tables. This is frequently set up as a column in one table (post) that stores primary key values from another table (user) so that it can join between the two. Foreign key constraint: A feature that causes the DBMS to enforce the integrity of foreign keys. It ensures that values in the foreign key column (post->user_id) refer to a valid entry in the other table (user->id). Constraints are a helpful convenience (aiding in the "C" in ACID), but they come at a cost: Inserts, updates, and deletes on tables with constraints require additional compute / IO to enforce. Removing them can result in performance improvements, but require shifting the maintenance of the relationship to the app-side. Apps need to ensure their transactions always leave these relationships in a valid state. Databases: the world of infinite tradeoffs.

Apply Image

Ben Dicken

@BenjDicken

@mjkhanga Of course! We want foreign keys, as that's what allows us to build relationships between tables. Wether or not the database enforces it on my behalf is the decision to make.