A database is not a file you happen to query: it is a system that stores data and also enforces rules about that data while many clients read and write it at the same time. Interviewers open with "DBMS vs RDBMS" and "primary key vs foreign key" because the answers reveal whether you see a database as passive storage or as the component that guarantees correctness. For a Backend or AI engineer this matters daily: every constraint you push into the database is a bug class you no longer have to defend against in five different services.
Plain file (orders.csv) DBMS (e.g. PostgreSQL, MySQL, SQLite)
----------------------- --------------------------------------
read whole file, filter in code querying : declarative SQL + optimizer + indexes
two writers overwrite each other concurrency : locks / MVCC so writers do not corrupt each other
crash mid-write = half-written file durability : write-ahead log, fsync, crash recovery
nothing stops "age = -5" constraints : PK, FK, UNIQUE, NOT NULL, CHECK
"transfer" = 2 separate writes transactions : all-or-nothing (atomic) groups of writes
anyone with file access sees all security : users, roles, GRANT/REVOKE, row-level policies
restore = hope you copied the file recovery : backups + log replay to a point in timeEach row of that table exists because a plain file fails in that exact way once you have concurrent users, crashes and bad input. The consequence for design: correctness rules (a salary must be positive, an order must belong to a real user) are cheapest and most reliable when the database enforces them, because the database is the single point every writer must go through. Application-level checks are still useful for friendly error messages, but they are a second line of defence, not the first.
DBMS = any software that manages a database (storage + access + integrity + recovery)
examples of data models it may use: hierarchical, network, key-value,
document, graph, relational
RDBMS = a DBMS whose data model is RELATIONAL:
data lives in tables (relations) = rows (tuples) x columns (attributes)
every row is identifiable by a key
tables are linked by keys (foreign keys), queried with SQL
typically ACID transactions
PostgreSQL, MySQL, SQL Server, Oracle, SQLite -> RDBMS
Redis, MongoDB, Cassandra, Neo4j -> DBMS, but not relational
users orders
+---------+-------+-----------+ +----------+---------+--------+
| user_id | name | email | | order_id | user_id | total |
+---------+-------+-----------+ +----------+---------+--------+
| 1 | Asha | [email protected] |<--------| 501 | 1 | 250.00 |
| 2 | Ravi | [email protected] | | 502 | 1 | 80.00 |
+---------+-------+-----------+ +----------+---------+--------+
table = relation row = tuple orders.user_id points at users.user_idEvery RDBMS is a DBMS but not the reverse. The useful interview framing is that "relational" is a data model and a set of guarantees (keys, referential integrity, a declarative query language), not a synonym for "SQL database". In practice the boundary is blurry: PostgreSQL stores JSON documents, and some document databases support multi-document transactions, so say "generally" rather than drawing a hard line, and name the guarantee you actually care about.
PRIMARY KEY : identifies a row. ONE per table (may be composite = several columns together).
implies NOT NULL + UNIQUE on those columns.
UNIQUE : no two rows share this value. MANY per table allowed.
NULL handling differs: SQLite/PostgreSQL/MySQL allow multiple NULLs;
SQL Server allows only one NULL in a plain UNIQUE constraint.
FOREIGN KEY : a column (or columns) that must match an existing key in a parent table (or be NULL).
enrollments (composite PK) employees
+------------+-----------+ +--------+---------+---------+
| student_id | course_id | | emp_id | email | dept_id |
+------------+-----------+ +--------+---------+---------+
| 1 | 101 | <- ok | 1 | [email protected] | 1 | PK = emp_id
| 1 | 102 | <- ok | 2 | [email protected] | 2 | UQ = email
| 1 | 101 | <- REJECTED +--------+---------+---------+ FK = dept_id -> departments
+------------+-----------+
the PAIR is the key, not each column aloneThe primary key answers "which row do I mean?", so there can only be one primary key definition per table, though it can span several columns (a junction table like enrollments is the classic case). UNIQUE answers "no duplicates here" and a table can have many of them, for example email, phone and username on a users table. A foreign key does something different: it points at another table's key and makes the database refuse orphan rows. A primary key protects a single table; a foreign key protects the relationship between two tables.
Parent row is deleted (or its key updated). What happens to child rows?
ON DELETE CASCADE : child rows are deleted too (badges follow their employee)
ON DELETE RESTRICT : the delete is refused (cannot delete an employee who has payroll rows)
ON DELETE SET NULL : child FK column becomes NULL (employees lose their department, keep their row)
NO ACTION (default) : refused at end of statement (deferrable in PostgreSQL; RESTRICT checks immediately)
Constraint Rule it enforces Example
------------------ --------------------------------------- --------------------------
NOT NULL value must be present email TEXT NOT NULL
UNIQUE no duplicates UNIQUE (email)
PRIMARY KEY NOT NULL + UNIQUE, identifies the row PRIMARY KEY (emp_id)
FOREIGN KEY value must exist in parent (or be NULL) REFERENCES departments(dept_id)
CHECK boolean rule on the row CHECK (salary > 0)
DEFAULT value used when none is supplied DEFAULT 'active'The referential action is a business decision, not a syntax detail. CASCADE is convenient for true parts-of-a-whole (an order's line items) but dangerous when a single delete can silently fan out through several levels and remove data nobody expected. RESTRICT forces a human or service to make an explicit decision and is the safer default for financial records. SET NULL suits optional relationships. Also note that a foreign key column may be NULL unless you add NOT NULL, meaning "no parent", which is how optional relationships are modelled.
An honest caveat: not every production system declares foreign keys. Some very large or sharded architectures skip them because a foreign key forces a lookup in the parent table on every insert and cannot span shards or separate databases, and some online schema-change tools work more easily without them. That is a deliberate trade of database-enforced integrity for scalability and operational flexibility, and the team then owns orphan-prevention in application code and cleanup jobs. For a single-node relational database, declaring them is generally the right default.
PRAGMA foreign_keys = ON; or your constraints silently do nothing.-- Run with: sqlite3 :memory:
PRAGMA foreign_keys = ON;
CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
salary INTEGER NOT NULL CHECK (salary > 0),
status TEXT NOT NULL DEFAULT 'active',
dept_id INTEGER REFERENCES departments(dept_id) ON DELETE SET NULL
);
CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER, PRIMARY KEY (student_id, course_id));
CREATE TABLE badges (badge_id INTEGER PRIMARY KEY, emp_id INTEGER NOT NULL REFERENCES employees(emp_id) ON DELETE CASCADE);
CREATE TABLE payroll (pay_id INTEGER PRIMARY KEY, emp_id INTEGER NOT NULL REFERENCES employees(emp_id) ON DELETE RESTRICT);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales');
INSERT INTO employees(emp_id,email,salary,dept_id) VALUES (1,'[email protected]',100,1),(2,'[email protected]',90,2),(3,'[email protected]',80,2);
INSERT INTO badges VALUES (10,1),(11,2);
INSERT INTO payroll VALUES (100,1);
SELECT * FROM employees;
-- emp_id email salary status dept_id
-- 1 [email protected] 100 active 1 (status filled by DEFAULT)
-- 2 [email protected] 90 active 2
-- 3 [email protected] 80 active 2
-- Each of these is rejected:
INSERT INTO employees(emp_id,email,salary,dept_id) VALUES (4,'[email protected]',50,1); -- UNIQUE constraint failed: employees.email
INSERT INTO employees(emp_id,email,salary,dept_id) VALUES (4,'[email protected]',-5,1); -- CHECK constraint failed: salary > 0
INSERT INTO employees(emp_id,email,salary,dept_id) VALUES (4,'[email protected]',50,99); -- FOREIGN KEY constraint failed
INSERT INTO employees(emp_id,email,salary) VALUES (4,NULL,50); -- NOT NULL constraint failed: employees.email
INSERT INTO enrollments VALUES (1,101),(1,102); -- ok: pair is the key
INSERT INTO enrollments VALUES (1,101); -- UNIQUE constraint failed: enrollments.student_id, enrollments.course_id
-- Referential actions:
DELETE FROM departments WHERE dept_id = 2; -- SET NULL
SELECT emp_id, dept_id FROM employees ORDER BY emp_id;
-- 1 1
-- 2 (NULL)
-- 3 (NULL)
DELETE FROM employees WHERE emp_id = 2; -- CASCADE removes badge 11
SELECT * FROM badges;
-- 10 1
DELETE FROM employees WHERE emp_id = 1; -- FOREIGN KEY constraint failed (RESTRICT: payroll row 100 exists)Q: What is the difference between a DBMS and an RDBMS?
A: A DBMS is any system that manages stored data with access, integrity and recovery features, whatever its data model. An RDBMS is a DBMS specifically built on the relational model: data in tables, rows identified by keys, relationships expressed through foreign keys, queried with SQL and usually with ACID transactions. The trade-off you should mention is that the relational model buys strong integrity and flexible ad hoc queries at the cost of a fixed schema and harder horizontal scaling than many non-relational systems.
Q: What is the difference between a primary key and a unique key?
A: Both prevent duplicate values, but a table has only one primary key definition (which may be composite) and it implies NOT NULL, while a table may have many UNIQUE constraints. The primary key is the row's identity and is what foreign keys usually reference; UNIQUE captures other business rules such as one account per email. The engine-specific detail is NULL handling: PostgreSQL, MySQL and SQLite allow multiple NULLs in a unique column, whereas SQL Server allows only one.
Q: What is the difference between a primary key and a foreign key?
A: A primary key uniquely identifies rows inside its own table, so it cannot repeat or be NULL. A foreign key is a column in a child table that must match an existing key in a parent table (or be NULL), so it can repeat freely: many orders can point at one user. The cost of the foreign key is an extra parent lookup on writes and restrictions on deleting parents, which is why some sharded systems leave it out and enforce the rule in application code.
Q: What are database constraints?
A: They are declarative rules the database enforces on every write: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT (DEFAULT supplies a value rather than rejecting one). Enforcing them centrally means every client, script and migration is covered, and the invariants survive application bugs. The cost is a little write overhead and less flexibility for bulk loads, which is why loaders sometimes disable and later re-validate them.
Follow-up: Can a table have two primary keys?
A: No, there is one primary key constraint per table, but it can consist of multiple columns (a composite key). If two different columns each need to be unique and not null, make one the primary key and give the other a UNIQUE plus NOT NULL constraint; these are usually called candidate keys.
Follow-up: Should you use a natural key or a surrogate key as the primary key?
A: A surrogate key (auto-increment integer or UUID) never changes and keeps foreign keys small, but it has no business meaning, so you must still add UNIQUE constraints on the natural identifier such as email or order number. A natural key avoids an extra column but breaks when the real-world value changes and forces the change to cascade through every referencing table. Most systems use a surrogate primary key plus unique constraints on natural identifiers.
Follow-up: If foreign keys are so useful, why do some large systems avoid them?
A: Each insert or delete must check the other table, which adds work and lock interaction on hot parent rows, and a foreign key cannot span shards or separate databases. Some online schema-migration workflows are also simpler without them. The price is that orphan rows become possible, so those teams add application checks, periodic reconciliation queries and soft deletes; it is a trade, not a free win.
When you design a service, decide which invariants must never be violated and put those in the database: unique email, positive amounts, an order that must reference a real user, a payment that must reference an order. These are the guarantees that hold when two service instances race, when a bug ships, or when someone runs a manual fix in production. For an AI or RAG backend the same thinking applies to metadata: a chunk row should reference its document with a foreign key and ON DELETE CASCADE so deleting a document cannot leave orphan chunks and embeddings behind. Once you grow past a single node and shard by a key, cross-shard foreign keys are no longer possible, so the design conversation shifts to which relationships stay co-located on the same shard (and keep their foreign keys) and which are enforced by application logic and reconciliation jobs.
Constraints trade a small amount of write throughput and schema rigidity for correctness that does not depend on every developer remembering every rule. Foreign keys cost an extra lookup per write, can turn a delete into a cascade with surprising blast radius, and make bulk loads, sharding and some zero-downtime migrations harder; CHECK constraints and unique indexes carry their own maintenance cost. Skipping them is the wrong choice for a single-node system where data integrity matters and the write rate is modest, because you then own every orphan and duplicate bug forever. Relying on them is the wrong choice when the data spans shards or services, where the constraint cannot be enforced anyway and you need reconciliation and idempotent writes instead.