Keys, constraints, indexes and transactions

Keys and constraints are rules the database enforces on every write. Indexes make reads fast. Transactions make a group of changes all-or-nothing. Interviewers ask about these as theory ("what is a foreign key?", "what is ACID?"), and a tester who can check data against these rules finds bugs that the UI hides. The examples use the company data.

Primary, unique and foreign keys

A primary key identifies each row. Its values are unique and never NULL, and a table has at most one. It may span several columns (a composite key), such as (order_id, line_no) in an order lines table.

A unique key is another "no repeats" rule, for example on email, and a table can have several. MySQL, PostgreSQL and SQLite allow several NULLs in a unique column by default, because one NULL is not equal to another.

A foreign key is a column whose values must exist in a key of another table (the parent table), or be NULL. A foreign key from orders.employee_id to employees.id stops orders for employees who do not exist. A row that points to a parent row that does not exist is called an orphan. Foreign key values repeat freely: one employee has many orders.

Before a team adds a key to existing data, a tester checks that the data obeys it. Can email become unique? Compare the row count with the count of distinct values:

SELECT COUNT(*) AS employees, COUNT(DISTINCT email) AS distinct_emails FROM employees;

employees | distinct_emails
10 | 9

Ten employees but nine distinct emails: one address is shared, and the new key would fail until it is fixed. Can orders.employee_id get a foreign key? Look for values with no matching employee:

SELECT o.id, o.employee_id
FROM orders o
LEFT JOIN employees e ON e.id = o.employee_id
WHERE o.employee_id IS NOT NULL AND e.id IS NULL;

id | employee_id
107 | 12

How did order 107 get in? SQLite, which runs this page, enforces foreign keys only when each connection runs PRAGMA foreign_keys = ON (PRAGMA is a SQLite command that changes a setting). MySQL (InnoDB) and PostgreSQL enforce declared foreign keys. Many real systems also skip foreign keys for speed, which is why orphan checks are part of database testing.

Other constraints

NOT NULL requires a value. CHECK (amount >= 0) rejects rows that break a condition; MySQL enforces CHECK from version 8.0.16, and earlier versions read it and ignored it. DEFAULT supplies a value when none is given; it never rejects anything. To test a constraint, insert one valid row and expect success, then one invalid row and expect an error.

Indexes

An index is a sorted copy of one or more columns, usually stored as a B-tree (a tree-shaped structure that is quick to search), that points back to the rows. With an index on orders(customer), the database can jump to one customer's rows instead of reading the whole table. Indexes help WHERE, joins and ORDER BY on the indexed columns. They cost extra storage and make inserts and updates slower, because each index must be updated too. A pattern that starts with a wildcard, such as LIKE '%Ltd', or a function wrapped around the column usually cannot use the index. To see whether a query uses one, read its plan (the list of steps the database takes to run it): EXPLAIN in MySQL, EXPLAIN ANALYZE in PostgreSQL, EXPLAIN QUERY PLAN in SQLite.

Transactions and ACID

A transaction groups statements so they succeed or fail together. BEGIN starts it, COMMIT saves it and ROLLBACK undoes everything since BEGIN:

BEGIN;
UPDATE employees SET salary = 0 WHERE dept_id = 1;
ROLLBACK;
SELECT name, salary FROM employees WHERE dept_id = 1;

name | salary
Asha Rao | 150000
Vikram Shah | 95000
Dev Malhotra | 95000

The salaries are unchanged. ACID names four guarantees. Atomicity: all or nothing, so a transfer never takes money from one account without adding it to the other. Consistency: each transaction moves the data from one valid state to another, with every constraint holding. Isolation: transactions running at the same time do not see each other's unfinished work; the level is configurable, and PostgreSQL defaults to the level called READ COMMITTED while MySQL InnoDB defaults to REPEATABLE READ. Durability: once committed, a change survives a crash.

An UPDATE that matches no row is not an error; it changes 0 rows. Code that moves money between two rows has to check the counts and roll back itself.

Normal forms in one minute

Normalisation stores each fact once. First normal form: one value per column, so no lists such as "Keyboard, Mouse" in one field. Second: every non-key column (a column that is not part of the key) depends on the whole key, not on part of a composite key. For example, in an order lines table keyed by (order_id, line_no), a customer_name column depends only on order_id, so it breaks this rule. Third: non-key columns depend only on the key, not on each other (store dept_id in employees, not the department's city). Teams sometimes break these rules on purpose, for example by copying a price onto an order line so a later price change does not rewrite old orders.

How to answer in an interview

Give a one-sentence definition, then a tester's check. "A foreign key makes sure a value exists in another table. Before one is added, I look for orphan rows with a left join." "ACID means a transaction is all-or-nothing, valid, isolated and durable; I would test atomicity by forcing a failure halfway and checking that nothing was saved."

Common mistakes

• Saying a foreign key must be unique. It must exist in the parent table; it may repeat.
• Saying a unique column can never hold NULL.
• Adding an index to every column without mentioning the cost to writes.
• Mixing up atomicity (all or nothing) and durability (committed changes survive).

Recap

Keys and constraints are rules on every write, and testers check existing data against them. Indexes speed up reads and slow down writes. Transactions give ACID, and normal forms keep each fact in one place.