Primary keys, foreign keys and changing data safely

Reading data is most of a screening test, but every job also involves adding and changing rows and understanding why the database refuses some changes. Those refusals come from keys and constraints. Knowing what each one promises lets you predict which statements succeed, which fail, and why a join returned extra rows.

Keys and constraints

• A primary key identifies one row. It cannot be NULL and cannot repeat. A table has at most one.
• A foreign key is a column whose values must exist as a primary key in another table. orders.customer_id referencing customers.id means you cannot create an order for a customer who does not exist.
• UNIQUE prevents duplicates but, unlike a primary key, usually allows NULL.
• NOT NULL requires a value. DEFAULT supplies one when none is given. CHECK enforces a rule such as quantity > 0.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  quantity INT CHECK (quantity > 0),
  status VARCHAR(20) DEFAULT 'new'
);

Example 1: inserting an order with customer_id 999 when no such customer exists fails with a foreign key error. Example 2: deleting a customer who has orders fails for the same reason, unless the foreign key was created with ON DELETE CASCADE, which deletes the orders too.

INSERT, UPDATE and DELETE

INSERT INTO orders (id, customer_id, quantity)
VALUES (101, 7, 3);

Status takes the default 'new'.

UPDATE orders SET status = 'shipped' WHERE id = 101;
DELETE FROM orders WHERE id = 101;

Example 3: UPDATE orders SET status = 'shipped'; with no WHERE marks every order as shipped. There is no confirmation prompt. The habit that prevents this is to write the SELECT first: SELECT * FROM orders WHERE id = 101;, check the row count, then change the SELECT to UPDATE or DELETE and keep the same WHERE.

Transactions

A transaction groups statements so they all succeed or all roll back. Example 4: moving 500 from one account to another is two UPDATE statements. Wrap them in BEGIN; and COMMIT;. If the second fails, ROLLBACK; undoes the first, and no money disappears. Many tools auto-commit each statement, so a DELETE run on its own cannot be rolled back afterwards. Start a transaction before risky changes on real data.

Common mistakes and a practice habit

• UPDATE or DELETE without WHERE.
• Inserting a duplicate primary key and not reading the error.
• Expecting UNIQUE to reject a second NULL.
• Deleting a parent row and assuming the child rows go with it.

Create the two tables above with five customers and ten orders. Try to break every constraint on purpose: duplicate key, missing customer, negative quantity, delete a parent. Read each error message in full. Then practise the SELECT-first habit until you cannot type UPDATE without a WHERE.