Database / SQLite Interview questions

Why does SQLite recommend enabling foreign keys explicitly, given they're off by default?

Foreign key enforcement was added to SQLite well after the file format and basic engine were already established and widely deployed, so turning it on by default risked breaking existing applications that had databases with foreign key declarations that weren't actually being enforced (and might have had inconsistent data as a result) — enabling enforcement retroactively on those datasets could suddenly cause previously "working" operations to start failing.

PRAGMA foreign_keys = ON;   -- must be set explicitly, per connection

For new applications, though, the recommendation is almost universally to enable it — without enforcement, a foreign key declaration is purely documentation, with no actual guarantee that referenced rows exist, silently allowing orphaned references that a real foreign key constraint is specifically meant to prevent. The historical compatibility reasoning applies to SQLite's own defaults, not to what's advisable for new schema design.

Take quiz

Why isn't foreign key enforcement on by default in SQLite? It's technically impossible to implement

✗ Not quite. Try again.

Enabling it by default retroactively could break existing applications with pre-existing inconsistent data

✓ Correct! Well done.

Foreign keys are a deprecated feature

✗ Not quite. Try again.

What happens to a declared but unenforced foreign key? It's automatically enforced anyway despite the pragma setting

✗ Not quite. Try again.

It's effectively just documentation, with no guarantee referenced rows actually exist

✓ Correct! Well done.

The CREATE TABLE statement fails to run

✗ Not quite. Try again.

Invest now in Acorns!!! 🚀 Join Acorns and get your $5 bonus!

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Earn passively and while sleeping

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.

More Related questions...

What is SQLite? What are the key features of SQLite? How is SQLite different from client-server databases like MySQL or PostgreSQL? What is the file format of a SQLite database? What are the supported data types in SQLite? What is dynamic typing (type affinity) in SQLite? How do you create a table in SQLite? How do you insert data into a SQLite table? How do you query data from a SQLite table? How do you update a record in SQLite? How do you delete a record in SQLite? What is a PRIMARY KEY in SQLite? What is AUTOINCREMENT in SQLite? What is a UNIQUE constraint in SQLite? What is a FOREIGN KEY in SQLite and how do you enable it? What is the sqlite3 command-line shell used for? How do you create an index in SQLite? What is a NULL value in SQLite? What is the difference between CHAR, VARCHAR, and TEXT in SQLite? What is the ROWID in a SQLite table? What is an in-memory SQLite database? How do you back up a SQLite database? What is the PRAGMA statement used for? What is a VIEW in SQLite? What is a TRIGGER in SQLite? What is the difference between DELETE, TRUNCATE, and DROP in SQLite? What is a transaction in SQLite and how do you use BEGIN/COMMIT/ROLLBACK? What is the difference between WAL mode and rollback journal mode? Why does SQLite recommend enabling foreign keys explicitly, given they're off by default? What is the difference between INTEGER PRIMARY KEY and a regular PRIMARY KEY in SQLite? How does SQLite handle concurrent writes? What is the difference between a SQLite VIEW and a TABLE? How do you perform a JOIN in SQLite? What is an UPSERT in SQLite (INSERT... ON CONFLICT)? How do you use the EXPLAIN QUERY PLAN statement? What is a composite primary key in SQLite? How does SQLite handle database locking? What is the difference between VACUUM and ANALYZE in SQLite? Explain the internal working of SQLite's B-tree storage structure? How does WAL mode improve concurrency compared to the default rollback journal? Explain the lifecycle of a SQLite transaction from BEGIN to COMMIT? Why might full-text search (FTS5) be needed instead of a LIKE query? How do you optimize a slow SQLite query? What are the limitations of SQLite for large-scale, high-concurrency applications? How does SQLite ensure ACID compliance without a separate server process? Explain how SQLite's write-ahead logging (WAL) checkpoint process works? What isolation level does SQLite provide, compared to other RDBMS? What is the STRICT keyword used for in SQLite table definitions?

Show more question and Answers...

Integration

	Interviews Questions Java Spring Hibernate Maven Testing API BigData Web Erlang AI Database Integration Cloud Python Scala Tools Golang	About Javapedia.net Javapedia.net is for Java and J2EE developers, technologist and college students who prepare of interview. Also this site includes many practical examples. This site is developed using J2EE technologies by Steve Antony, a senior Developer/lead at one of the logistics based company.
	contact: javatutorials2016[at]gmail[dot]com
Kindly consider donating for maintaining this website. Thanks.
	Copyright © 2026, javapedia.net, all rights reserved. privacy policy.

Database / SQLite Interview questions

Why does SQLite recommend enabling foreign keys explicitly, given they're off by default?

Comments & Discussions

Recently added...