Unit 6: Building tables and changing data
Create tables with the right rules, add, change and remove rows safely, group changes into transactions, and design tables that don't contradict themselves.
The big picture
So far you have only read data. Real systems also write it — and writing is where data gets damaged. Constraints stop bad rows getting in, transactions stop half-finished changes, and normalisation stops the same fact being stored twice and drifting apart.
Lessons in this unit
- CREATE TABLE and ALTER TABLE: CREATE TABLE defines a new table — its name, its columns, their types and rules — and ALTER TABLE changes an existing table, for example by adding a column.
- Constraints: the rules that keep bad data out: Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.
- INSERT, UPDATE and DELETE: INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE act on every row their WHERE matches, and on every row of the table if there is no WHERE.
- Transactions and ACID: A transaction groups several statements into one all-or-nothing unit: BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them. ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.
- Normalisation: designing tables that don't contradict themselves: Normalisation is designing tables so that each fact is stored in exactly one place. The usual target is third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.
- Views: saved queries you can use like tables: A view is a named, saved SELECT that you query like a table. It stores no data of its own, so it always shows the current data. A materialised view stores the result and must be refreshed.
- DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP: SQL commands fall into four families: DDL changes structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK). DELETE removes rows, TRUNCATE removes all rows, and DROP removes the whole table.