In a versioned schema change set, what are reference or lookup seed rows, and why are they shipped with the schema?
answer
- data the schema itself depends on
- ships in the diff, not by hand
- stable codes, not generated numbers
- insert only where the key is absent
- demo data is not reference data
basics
~20 sReference rows are fixed lookup values the code and the constraints depend on: statuses, roles, currencies, category codes. Shipping them as guarded inserts in the ordered change set makes every database start identical and reviewable, instead of hand-inserted per environment.
solid answer
~50 sA **reference row** is data the application treats as part of the schema rather than as user data: a status enumeration, a role, a currency or country list, the default row a foreign key must point at. Because code and constraints depend on it existing, it belongs with the change that created the table — an insert in the same ordered change set, applied in the same order in every environment. Two properties matter. Give each row a **stable, explicitly assigned key** (a natural code, or an identifier you choose) rather than letting the database generate one, so the same logical row has the same identity everywhere. And write the insert so a second run is harmless — insert only where that key is absent. Anything that differs per environment, such as demo accounts, is not reference data and should load by a separate, clearly labelled path.
go deeper
Recall the distinction: reference rows are values the code depends on, so they ship with the schema change; user data does not. Know that the insert should be guarded so running it twice is harmless.
Explain why generated identifiers are wrong for lookup rows and how a stable code plus a unique constraint makes a conditional insert reliable. Be able to name what should be excluded from the change set.
Show judgment about the boundary: which lists are team-owned and belong in the history, which are user-editable and must be seeded once, and how you keep environment-only data from ever reaching production.
Frame it as ownership. Deciding which data is part of the delivered artifact and which is operational state determines what a rebuild-from-empty produces, and that is the contract every environment and every recovery drill depends on.
## What counts as a reference row Schema changes are usually discussed as structure — tables, columns, indexes, constraints. In practice a change set also carries a small amount of **data that the structure and the code assume is there**. Typical examples: - enumerated statuses or types that application logic branches on (`order_status`, `payment_method`); - roles and permission names a security check compares against; - currencies, countries, units, tax categories — slow-moving lists maintained by the team, not by users; - a default or system row that a newly added non-null foreign key must point at; - configuration or feature keys the application reads at boot. The common property is that this data is **owned by the change set, not by users**. Nobody creates a new order status through the UI; a developer adds one in a diff. That is the test to apply: if the row would be reviewed in a pull request rather than entered by a customer, it is reference data. ## Why it ships with the schema The alternative — a runbook line saying "remember to insert the three statuses" — fails in ordinary ways. A new developer's local database is missing them and the application crashes at boot. A rebuilt test environment silently has two of the three, so one code path is never exercised. Production drifts from staging and nobody can say when. Shipping the rows as part of the ordered change history fixes all of that at once: | Concern | Rows in the change set | Rows inserted by hand | | --- | --- | --- | | Every environment identical | Yes, by construction | Only if nobody forgets | | Reviewed | In the same diff as the schema | Not reviewed at all | | Ordering | Guaranteed after the table exists | Whenever someone remembers | | History of a change | The added row is a dated entry | Lost | | Recreating a database from empty | Works | Needs tribal knowledge | ## Keys must be stable, not generated The most common mistake is to let the database assign identifiers to lookup rows. Each database that runs the change independently assigns its own numbers, so the same logical status is `3` in one environment and `4` in another. Anything keyed on the number — code constants, exported fixtures, rows copied between environments — then diverges, and the divergence is invisible until something joins across it. The fixes are simple: 1. Give the lookup table a **natural key** — a short stable code such as `AWAITING_PAYMENT` — and make foreign keys and code refer to that. 2. If a numeric key is required, **assign the numbers explicitly** in the insert rather than letting them be generated, and never reuse a number for a different meaning. 3. Keep the code side referring to the stable code, and translate once at the boundary, instead of scattering literal numbers. ## Write the insert so a re-run is harmless A change set entry may be applied to a database that already contains some of the rows — a database restored from a snapshot, an environment where an earlier attempt half-succeeded, or a later change that re-asserts the full list. A bare insert either fails on a unique constraint or, worse, duplicates the row. Instead, make the row's key unique and insert conditionally: ```sql INSERT INTO order_status (code, label) SELECT 'AWAITING_PAYMENT', 'Awaiting payment' WHERE NOT EXISTS (SELECT 1 FROM order_status WHERE code = 'AWAITING_PAYMENT'); ``` The same shape covers updating a label: an update predicated on the code touches the right row whether or not it has been touched before. The unique constraint is what makes the guard trustworthy; without it, two concurrent runs can both see "absent" and both insert. ## What is not reference data Keep the boundary tight. Demo customers, a sample catalogue, load-test volumes and screenshot data are **environment-specific** and do not belong in the change set: they would be applied to production too, and they are usually large, noisy and frequently regenerated. Load them from a separate, explicitly invoked path that never runs in production. The same goes for anything a user can subsequently edit — once users own a row, re-asserting it from a change set silently overwrites their work. If the list is genuinely user-maintainable, ship an initial set once and never re-assert it.
- A lookup table's rows are referred to in code by their numeric identifier. What is the safer alternative?Refer to a short stable code (`AWAITING_PAYMENT`) that the change set assigns explicitly, and put a unique constraint on it. Numeric identifiers generated independently in each database differ per environment; a code written into the insert is the same everywhere, survives a rebuild from empty, and reads as itself in a query result.
- Where should environment-specific data such as demo accounts be loaded, if not in the change set?Through a separately invoked path that is never wired into the deploy — a labelled script or task run on demand against non-production databases. Keeping it out of the ordered change history stops it reaching production, keeps the history small, and lets it be regenerated freely without becoming part of the schema's permanent record.
- A reference row can also be edited by administrators in the application. What breaks if a later change re-asserts it?The re-assertion overwrites the edit, silently, on every environment where an administrator had changed it. Once a row is user-editable it is no longer purely reference data: seed it once with a guarded insert that does nothing when the key exists, and never issue an unconditional update against it afterwards.
saying these in an interview costs you the question
- Treats reference rows as test data to be inserted manually in each environment.
- Lets the database generate lookup identifiers, then hard-codes those numbers in application code.
- Writes a bare insert with no guard, so a second run fails or duplicates the row.
- Puts demo accounts and sample catalogues in the same change set as real reference data.
- Assumes production already has the rows because someone inserted them once by hand.
- Re-asserts rows administrators can edit, quietly overwriting their changes.