SQL / RELATIONAL FOUNDATIONS
Rows, columns, keys, and the shape of a table
Read a table as fixed typed columns over unordered rows, and declare primary, composite, and natural keys that keep each row's identity unique.
What you will learn
- Describe a table by its degree (columns) and its cardinality (rows)
- Declare a PRIMARY KEY so no two rows can claim the same identity
- Put a UNIQUE constraint on a composite natural key like (row_label, seat_number)
- Tell a NULL cell apart from an empty string in the same column
Understanding Rows, columns, keys, and the shape of a table
A table's columns are declared once and then describe every row the same way: each column has a name, a type, and a rule about whether the value may be missing. That header is a contract. Writing seat_number integer NOT NULL means every row carries a seat_number and it is a whole number, so the server can refuse bad data instead of storing it. The count of columns is the table's degree and changes only when you run ALTER TABLE; the count of rows is its cardinality and changes with every INSERT and DELETE. The shape of a table is that pair: a fixed width and a moving height.
A row is one complete statement about one thing, and that thing should be nameable in the singular: one seat on one flight, not the whole seating chart. Unlike a spreadsheet, rows are not numbered and cells have no addresses, so there is no A1 and no third row to point at. You reach a value by naming its column and describing the row you want, which means every fact you will ever need to search by has to be stored as a column value inside that row.
Because there are no row numbers, identity has to live in the data itself. Any set of columns whose combined value is unique across all rows, and which cannot be trimmed any smaller, is a candidate key; the one you designate as PRIMARY KEY is implicitly unique and NOT NULL, and the server backs it with an index so the check is cheap. A key can span several columns: (row_label, seat_number) is unique even though neither column is on its own, so you can keep that natural key as a UNIQUE constraint while a short surrogate integer acts as the primary key. Prefer keys that never change, because other tables refer to a row by copying its key value, and a key that changes drags every reference with it.
Nothing else in the row is affected by the choice of key, but everything downstream depends on it being honest: if the constraint is missing, duplicates arrive silently and no later query can tell them apart.
CREATE TABLE seat (
seat_id integer PRIMARY KEY,
row_label text NOT NULL,
seat_number integer NOT NULL,
passenger text,
UNIQUE (row_label, seat_number)
);
INSERT INTO seat (seat_id, row_label, seat_number, passenger) VALUES
(1, 'A', 1, 'Okonkwo'),
(2, 'A', 2, NULL),
(3, 'B', 1, 'Vasquez');
SELECT seat_id, row_label, seat_number, passenger
FROM seat
ORDER BY seat_id;The column list fixes what a row is allowed to say, and a key is the constraint that no two rows say it about the same thing.
Worked examples
The primary key rejects a repeat
A duplicate seat_id is refused even though every other value in the row is new (PostgreSQL output).
INSERT INTO seat (seat_id, row_label, seat_number, passenger)
VALUES (1, 'C', 4, 'Haddad');Example explained
Line 1Seat C4 does not exist yet, so the row is new in every way except seat_id, and that alone is enough.
Line 2The constraint was never given a name, so PostgreSQL generated seat_pkey when the PRIMARY KEY was declared.
Line 3The DETAIL line prints the colliding key value, which tells you exactly which existing row you hit.
Line 4The statement fails as a whole; no partial row is left behind.
A composite key blocks a double booking
Uniqueness declared over two columns treats the pair as one value, so seat A1 cannot be sold twice.
INSERT INTO seat (seat_id, row_label, seat_number, passenger)
VALUES (4, 'A', 1, 'Haddad');Example explained
Line 1seat_id 4 is unused, so the primary key is perfectly happy with this row.
Line 2UNIQUE (row_label, seat_number) is checked as a single combined value, and ('A', 1) belongs to seat_id 1.
Line 3That pair is a second candidate key: it identifies a row just as precisely as seat_id does.
Line 4The generated name follows the table_column_column_key pattern, which is how you spot which constraint fired.
Counting degree and cardinality
Reads the two numbers that define a table's shape, one from the data and one from the catalog.
SELECT (SELECT count(*) FROM seat) AS row_count,
(SELECT count(*) FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'seat') AS column_count;Example explained
Line 1count(*) over seat gives the cardinality, which changes with every INSERT and DELETE.
Line 2information_schema.columns holds one row per column, so counting it gives the degree, which only ALTER TABLE changes.
Line 3Filtering on table_schema keeps a same-named table in another schema from inflating the count.
Line 4Each subquery returns a single value, so the whole statement yields exactly one row.
Important notes
A table has no built-in row order. The rows above match the INSERT order only because of ORDER BY seat_id; without it the server may return any order it finds cheapest, and that order can change later.
psql prints NULL as an empty cell by default, which is why seat 2 looks blank; \pset null '(null)' makes it visible. Other clients show NULL or (null), so a blank cell alone never proves the value is NULL.
Common mistakes
Storing repeats in extra columns (seat_1, seat_2, seat_3) instead of extra rows: a fourth seat then needs ALTER TABLE, and "which seats are free" cannot be answered by one WHERE clause.
Choosing a value that can change as the primary key, such as an email address or a seat label: the day it changes, every row elsewhere that copied it points at nothing.
Writing '' for an unassigned seat instead of NULL: WHERE passenger IS NULL then returns zero rows, and count(passenger) counts the empty strings as real passengers.
Try it yourself
Change, predict, then run
In the editor, create a locker table with locker_id integer PRIMARY KEY, NOT NULL building and unit_number columns, a nullable assigned_to, and UNIQUE (building, unit_number). Insert three lockers, then insert a fourth that reuses an existing building and unit_number pair and note which constraint name appears in the error.
Open the SQL workspaceCheck your understanding
A seat table has seat_id integer PRIMARY KEY and UNIQUE (row_label, seat_number), and already holds (1, 'A', 1) and (2, 'A', 2). Which insert is rejected?
- (4, 'A', 3, NULL) because passenger is missing, so the row is incomplete
- (4, 'B', 1, NULL) because seat_number 1 is already used in the table
- (4, 'A', 1, NULL) because the seat_id is free but seat A1 is already taken
- (4, 'a', 1, NULL) because 'a' and 'A' count as the same key value
Show answer
The unique constraint applies to the pair (row_label, seat_number) as one combined value, so a fresh seat_id does not rescue a row whose pair ('A', 1) already exists. ('B', 1) is accepted precisely because the pair differs even though seat_number repeats; passenger has no NOT NULL constraint, so leaving it NULL is legal; and under a case-sensitive collation 'a' is simply a different value from 'A'.