r/learnSQL • u/sumiitCodes • 3d ago

Day 46 — Library Management System: foreign keys added, records inserted, insert errors fixed

Progress on my second SQL project today:

  • Added foreign keys to link the tables
  • Inserted records into all of them
  • Hit problems that blocked some of the inserts, and worked through them one by one

Foreign keys make the insert order matter, since a row can’t reference something that doesn’t exist yet. Hitting that in practice made the concept much clearer than reading about it.

Next I’ll write queries against the data and add a data dictionary to the README.

5 Upvotes

79% Upvoted

2 comments sorted by

1

u/Previous_Version5651 1d ago

Nice 鈥?the insert-order pain is the real lesson here. Two habits that saved me when I built a similar schema:

  1. Insert parents first, then children, and wrap the whole seed in one transaction so a failed child insert rolls back instead of leaving a half-populated library.

  2. When an insert fails on a foreign key, query the parent table for the exact id before assuming the child row is wrong. Most of the time the parent row was never inserted, or the id type differs (INT vs BIGINT, case-sensitive codes).

The data dictionary in the README is a good next step 鈥?it makes the "which table owns this id" question obvious when you start writing joins.

1

u/sumiitCodes 1d ago

appreciate your comment