Prev Next

Database / SQLite Interview questions

What is a FOREIGN KEY in SQLite and how do you enable it?

A FOREIGN KEY constraint links a column in one table to a primary/unique key in another, ensuring referential integrity — you can't insert a row referencing a value that doesn't exist in the referenced table.

CREATE TABLE order_item (
    id INTEGER PRIMARY KEY,
    person_id INTEGER,
    FOREIGN KEY (person_id) REFERENCES person(id)
);

Notably, SQLite parses foreign key constraints but doesn't actually enforce them by default, for historical backward-compatibility reasons — you must explicitly turn enforcement on per connection with:

PRAGMA foreign_keys = ON;

This is a common surprise for developers coming from other databases where foreign keys are always enforced: forgetting this pragma means orphaned references can silently be inserted with no error at all.

Does SQLite enforce foreign key constraints by default?
What PRAGMA statement enables foreign key enforcement?

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...

Google Spanner Database Interview questions

Comments & Discussions

Interviews Questions

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.

Follow Javapedia updates

contact: javatutorials2016[at]gmail[dot]com

Kindly consider donating for maintaining this website. Thanks.

Copyright © 2026, javapedia.net, all rights reserved. · .

DMCA.com Protection Status