Practise vocabulary for reviewing database schema changes in PRs: migration vocabulary, review comments, backward compatibility, and naming conventions.
0 / 14 completed
1 / 14
Adding a NOT NULL column to an existing production table without a default value is a ___ change — it will fail on all existing rows.
Adding a NOT NULL column without a default breaks existing rows (they'd have NULL in the new column, violating the constraint). Safe alternatives: add as nullable first, backfill, then add the NOT NULL constraint separately.
2 / 14
The safest pattern for renaming a column in a live production database is to ___.
Phased column renaming avoids downtime: (1) add new column, (2) write to both, (3) backfill, (4) update all readers to use new column, (5) drop old column. A single rename with active traffic causes immediate breakage.
3 / 14
A ___ migration modifies the schema without backfilling existing data — some rows will have the old structure until a separate job runs.
A lazy migration applies the schema change but doesn't immediately update existing rows. This is common for large tables where a full backfill would lock the table. Application code must handle both old and new data formats during the transition.
4 / 14
In schema review, 'this index is covering for the query' means the index ___.
A covering index includes all columns referenced by a query (in WHERE, JOIN, and SELECT). The database engine can satisfy the entire query from the index without touching the main table (heap), dramatically improving performance.
5 / 14
Using ___ as a primary key instead of natural keys (email, username) avoids issues when natural key values change.
Surrogate keys (UUID, SERIAL) are artificial identifiers with no business meaning. They never change, making schema evolution easier. Natural keys (email, username) can change, causing complex cascade updates.
6 / 14
Sarah: 'I've just updated the user profile schema to include a `verified_email` boolean field. It should automatically mark users with verified emails as true. But some users are still showing as unverified in the dashboard!'
Which of the following is the MOST likely reason for this issue, considering best practices for data migration and schema changes?
The core problem here stems from neglecting data migration. Simply adding a new field to the schema doesn't automatically populate it with values for existing records. Sarah's observation indicates that the `verified_email` column was not updated during the migration process – therefore, users who hadn't previously verified their email would remain unverified in the dashboard. Options A and D are incorrect because they misdiagnose unrelated issues (UI updates & indexing). Option C is possible but less likely than a simple data migration failure.
7 / 14
Sarah: 'I've just updated the user profile schema to include a `verified_email` boolean field. It should automatically mark users with verified emails as true. But some users are still showing as unverified in the dashboard!'
Which of the following is the MOST likely reason for this issue, considering best practices for data migration and schema changes?
The core problem here stems from neglecting data migration. Simply adding a new field to the schema doesn't automatically populate it with values for existing records. Sarah's observation indicates that the `verified_email` column was not updated during the migration process – therefore, users who hadn't previously verified their email would remain unverified in the dashboard. Options A and D are incorrect because they misdiagnose unrelated issues (UI updates & indexing). Option C is possible but less likely than a simple data migration failure.
8 / 14
Sarah: 'I've just updated the user profile schema to include a `verified_email` boolean field. It should automatically mark users with verified emails as true. But some users are still showing as unverified in the dashboard!'
Which of the following is the MOST likely reason for this issue, considering best practices for data migration and schema changes?
The core problem here stems from neglecting data migration. Simply adding a new field to the schema doesn't automatically populate it with values for existing records. Sarah's observation indicates that the `verified_email` column was not updated during the migration process – therefore, users who hadn't previously verified their email would remain unverified in the dashboard. Options A and D are incorrect because they misdiagnose unrelated issues (UI updates & indexing). Option C is possible but less likely than a simple data migration failure.
9 / 14
Sarah: 'I've just updated the user profile schema to include a `verified_email` boolean field. It should automatically mark users with verified emails as true. But some users are still showing as unverified in the dashboard!'
Which of the following is the MOST likely reason for this issue, considering best practices for data migration and schema changes?
The core problem here stems from neglecting data migration. Simply adding a new field to the schema doesn't automatically populate it with values for existing records. Sarah's observation indicates that the `verified_email` column was not updated during the migration process – therefore, users who hadn't previously verified their email would remain unverified in the dashboard. Options A and D are incorrect because they misdiagnose unrelated issues (UI updates & indexing). Option C is possible but less likely than a simple data migration failure.
10 / 14
Mark just commented on a PR proposing a new `transaction_id` column in the `orders` table. He writes: 'To ensure data integrity, we should add a NOT NULL constraint and a default value of 0 to this field.' What's Mark primarily concerned about?
order_id is currently nullable and could be missing.
Mark's concern is about data integrity. Adding a NOT NULL constraint without a default value will cause validation errors on all existing rows where `order_id` doesn't have a corresponding `transaction_id`. This highlights the importance of considering the impact on existing data when introducing schema changes, especially constraints.
11 / 14
During a code review, David points out that you're renaming a column in the `products` table from `item_name` to `product_title`. He suggests using a rolling back script if anything goes wrong. Why is this approach recommended?
You've implemented complex cascading updates across multiple tables
David's suggestion reflects best practices for database schema changes. Rolling back scripts offer a controlled method to revert the change if it introduces errors or conflicts. This is crucial because renaming columns can have cascading effects across multiple tables and applications, making a rollback mechanism essential.
12 / 14
"I'm planning to migrate the `users` schema by adding a new `last_login` timestamp column. I want to ensure existing data is correctly populated without impacting user sessions." Which migration strategy would be most appropriate?
The migration needs to run in parallel with the main application
An incremental migration is ideal for scenarios like this where you need to update an existing schema while minimizing disruption. This approach allows you to populate the `last_login` column row by row, ensuring that user sessions aren't affected and allowing for monitoring and validation during the process.
13 / 14
During a standup meeting, Emily explains: 'We're adding an index on `user_id` in the `posts` table. This should significantly speed up queries that filter posts by user.' What does Emily *specifically* mean when she says 'this index is covering for the query'?
The query only needs to read from this single table
When an index 'covers' a query, it means that all the columns needed by the `WHERE` clause are included in the index itself. This allows the database to retrieve the data directly from the index without needing to access the base table rows, dramatically improving performance. The key is direct data access.
14 / 14
In a Slack channel discussing schema changes, Alex says: 'Using an `email` address as the primary key avoids issues when email addresses change.' What is Alex referring to?
A primary key must be unique and immutable
Alex correctly identifies the core benefit of using a flexible attribute like an email address as a primary key. Primary keys must be unique and immutable; changing email addresses would violate this constraint, leading to data integrity problems. Using a stable identifier like an email avoids these issues.
What does the "Schema Review Language" exercise practise?
Practise vocabulary for reviewing database schema changes in PRs: migration vocabulary, review comments, backward compatibility, and naming conventions.
How many questions are in this exercise?
This exercise has 14 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database Schema Design category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Schema Review Language" part of a larger series?
Yes — it's one exercise in the Database Schema Design category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database Schema Design category page for related exercises, or browse the main Exercises hub for other IT English topics.