Data Integrity: Foreign Keys and Uniqueness Constraints

Episode #7 in the Inside The Muse Series

by Jack Kinsella

@semicolonandson

Here I continue on from the last episode (null constraints, etc.) in exploring ways to use an SQL database to ensure data integrity. I'll show ways to avoid shooting yourself in the foot by setting non-existant relationships or by deleting rows that are referenced elsewhere in the database and are therefore necessary. I'll show how to lean on foreign keys to build resource allocation features with practically no backend code. Next I'll demonstrate the perils of relying on uniqueness validations at the Ruby/PHP/Python backend level. Finally I'll show to avoid bloat in pivot tables for many-to-many relationships.

Show Notes

References

Postgresql Documentation on Constraints - covers uniquness constraints and foreign key constraints

Want To Become A Stronger Programmer?

Our screencasts are aimed at intermediate and advanced programmers and focus on:

  • Timeless technologies like unix, vim, SQL, program design, software testing etc. (so your learning lasts 30 years instead of 30 months)
  • Production codebases (instead of toy examples)
  • Softer skills (especially online marketing) necessary to flourish as a financially independent software developer
  • Mailing list members receive exclusive content as well as episode alerts. Never spam.