Tables, rows, and columns
A relational database keeps each kind of fact in its own table. The employees table holds people, departments holds teams, projects holds initiatives — one table per category, never mixed. Inside a table, every row is a single record (one employee) and every column is one attribute of that record (a name, a salary, a hire date).
Think of a table as a spreadsheet with strict rules: each column holds one kind of value, and every row has the same columns in the same order. That discipline is what makes a database queryable in a way a pile of free-form notes never is. You ask for name and salary, and the database knows exactly where to look.
erDiagram
DEPARTMENTS ||--o{ EMPLOYEES : "has"
DEPARTMENTS ||--o{ PROJECTS : "owns"
DEPARTMENTS {
int id
text name
text city
}
EMPLOYEES {
int id
text name
int department_id
text role
int salary
text hired_on
}
PROJECTS {
int id
text name
int department_id
text status
int budget
}
Primary keys: the ID of a row
Every table needs a way to point at one specific row — to update it, delete it, or link it from elsewhere. That job belongs to the primary key, a column whose value is unique for every row and never NULL. In this database, id is the primary key of each table: no two employees share an id, so id 4 always means Dina and nothing else.
Uniqueness is the whole point. A column like name is not a safe key, because two people can share a name; a column like salary repeats constantly. A primary key guarantees that one value picks out exactly one row, forever, which is the foundation every relationship is built on.
Foreign keys: the glue between tables
Splitting data across tables only works if you can stitch it back together. A foreign key is a column that stores the primary key of a row in another table. employees.department_id holds a departments.id value, so each employee row carries a pointer to the department it belongs to.
Unlike a primary key, a foreign key may repeat — every engineer points at the same Engineering row — and it may be NULL, meaning "no relationship." Omar's department_id is NULL because he is a contractor with no department. That nullable pointer is what makes a relationship optional rather than mandatory.
flowchart LR P["departments.id = 3<br/>PRIMARY KEY<br/>one row"] --> A["employees.department_id = 3"] P --> B["employees.department_id = 3"] P --> C["employees.department_id = 3"] N["employees.department_id = NULL<br/>optional link"] -.-> P style P fill:#0e7490,color:#fff style N fill:#b45309,color:#fff
SELECT id, name, city FROM departments;
SELECT id, name, department_id FROM employees WHERE department_id = 1;
Keys are not always one column
A common assumption is that a primary key must be a single column. It does not. When no single column is unique on its own, a composite key combines two or more columns whose values are unique only as a set. An enrolment table keyed by (student_id, course_id) has no unique single column, yet each pair appears once — one row per student per course.
Composite keys are common in junction tables that link two entities. The company database happens to use simple single-column ids, but recognising a composite key when you meet one stops you from assuming a table is missing its primary key just because no single column looks unique.
Natural keys versus surrogate keys
A primary key can be drawn from real data or invented. A natural key uses a value that already exists in the world — a national ID, a vehicle registration, a product code. It is meaningful but fragile: real-world identifiers change, get reused, or turn out not to be unique after all.
A surrogate key is an artificial value, usually an auto-incrementing integer, that exists only to identify the row. The id columns in this database are surrogates — they carry no business meaning, which is exactly their strength. They never change because a real-world fact changed, so relationships built on them stay stable.
Referential integrity
A foreign key is a promise: if it holds a value, that value must exist as a primary key somewhere in the parent table. Pointing an employee at department_id = 99 when no department 99 exists would create a dangling reference — a row that claims a relationship that is not really there. Databases enforce referential integrity to forbid exactly that.
NULL is the one exception, because NULL means "no value" rather than "a value that fails to match." A NULL foreign key is permitted precisely because it asserts nothing — the row simply has no parent, not a broken link to one.
In the company database, which column in the employees table is a foreign key that links to departments?
department_id stores the primary key value of the department each employee belongs to, creating the relationship between the two tables.
List the name and city of every department.
SELECT name, city FROM departments ;
Select the two columns from the
departmentstable.No filter is needed — return every row.
SELECT name, city FROM departments;
List every employee's name and department_id. Include the contractor who has no department.
SELECT name, department_id FROM employees ;
Select
nameanddepartment_idfromemployees.Do not add a WHERE clause — NULL values will appear automatically.
SELECT name, department_id FROM employees;
Find the employee whose foreign key is empty — the one with no department. Return their id and name.
SELECT id, name FROM employees -- filter for the missing foreign key here ;
A foreign key can be NULL when the relationship is optional.
Test for it with
WHERE department_id IS NULL.
SELECT id, name FROM employees WHERE department_id IS NULL;
Which statement about primary keys is correct?
Uniqueness is what defines a primary key, not the column count. A composite key spans several columns whose combination is unique; primary-key values are never duplicated and never NULL.
Recap
- A table holds one kind of record; each row is one record and each column one attribute.
- A primary key uniquely identifies a row — unique, never NULL, and sometimes a composite of several columns.
- A foreign key stores another table's primary key, creating a relationship between the two.
- Foreign keys may repeat (many children, one parent) and may be NULL (an optional relationship).
- Referential integrity forbids a foreign key from pointing at a row that does not exist — except NULL, which means no pointer at all.
Next you will follow these foreign-key pointers with joins, reassembling data that was deliberately split apart.