Tables, Rows, Columns and Primary Keys

Open any school class list and you can see the whole idea of a relational database in miniature. Each student has a line. Each line has the same boxes to fill in: first name, last name, grade. And somewhere there is usually an ID number, because two students can share a name but never share an ID.

Those four ideas, the table, the row, the column and the key, are the base of every relational database. This entry explains each one with a small class list and shows how a primary key keeps records apart and how a foreign key connects two tables. It stays on the vocabulary. Writing queries is covered in reading data with SELECT.

The table: one kind of thing

A table holds one kind of thing. A students table holds students. A courses table would hold courses. Mixing the two in one table makes it harder to keep the data correct.

PostgreSQL's documentation puts it plainly: a table is a named collection of rows, every row in a table has the same set of named columns, and each column has a data type. Databases are built from many such tables, and the links between the tables are what make them powerful.

Rows and columns

A column describes one property that every record has, such as a first name. A row is one record, such as one student. You can remember the difference this way: columns run down the page and set the labels, rows run across the page and carry the facts.

Here is the students table:

The table has four columns and four rows. Two of the rows hold the same name, "Sam Lee", but they are different students in different grades. If you only had the name, you could not tell which Sam someone meant. That problem is exactly what a primary key solves.

One more detail about rows: SQL does not promise any particular row order. If you need a list sorted by last name, you must ask for it. Do not assume the order you typed the rows in is the order they will come back.

Data types and empty values

Each column accepts one kind of value. The common kinds are whole numbers, numbers with decimals, text, dates and times. A column of whole numbers will reject the word "seven". This is helpful: the database catches many typing mistakes before they spread.

NULL: a value that is missing

Sometimes a value is unknown or does not apply. SQL uses NULL to mean "no value here". NULL is not zero and not an empty string. It means the fact is absent. If a student's grade level is not yet known, that cell can be NULL. Columns that must always have a value can be marked NOT NULL, and the database will then refuse a row that leaves it empty.

What a primary key is

A primary key is a column, or a small group of columns, whose value is different in every row and is never empty. It is the row's identity. In the students table, student_id is the primary key. Students 101 and 103 share a name, but the numbers 101 and 103 tell them apart.

PostgreSQL's documentation describes a primary key as the combination of two rules: values must be unique and must not be NULL. A table can have only one primary key. The database also builds an index on it, so that finding a row by its key is fast.

A short example in standard SQL:

CREATE TABLE students (
  student_id  INTEGER PRIMARY KEY,
  first_name  VARCHAR(50) NOT NULL,
  last_name   VARCHAR(50) NOT NULL,
  grade_level INTEGER NOT NULL
);

If you now try to insert a second row with student_id 101, the database rejects it. That refusal is the protection you want.

A difference between systems

Not every database follows the standard strictly. SQLite's documentation notes that, for historical reasons, it allows NULL values in most primary key columns unless the column is declared NOT NULL, is an integer primary key, or belongs to a WITHOUT ROWID or STRICT table. When you learn on one system and work on another, check the documentation for this kind of detail.

Choosing a primary key

Good keys share three traits: they are unique, they never change, and they are never empty. Names, email addresses and phone numbers fail the "never change" test, and names also fail the "unique" test.

There are two common approaches:

• A made-up number, called a surrogate key. The student_id column is one. It means nothing outside the database, so it never needs to change. Most tables use this approach, often with the database generating the numbers automatically. The way to ask for automatic numbering differs between systems.
• A real-world value, called a natural key, such as a country code or a course code that the school guarantees to be unique. These work only when you are sure the value will stay unique and stable.

Keys made of more than one column

Sometimes no single column identifies a row, but a pair does. Consider an enrollments table that records which student takes which course. One student takes several courses, and one course has several students. The pair of student and course is unique, because a student cannot enroll in the same course twice:

Here neither column is unique alone, since 101 appears twice and ART1 appears twice. Together they are. A key built from several columns is called a composite key.

Foreign keys: pointing at another table's key

The student_id column in enrollments does a second job. It refers back to a row in students. A column that refers to the primary key of another table is a foreign key.

CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL REFERENCES students (student_id),
  course_code VARCHAR(10) NOT NULL,
  PRIMARY KEY (student_id, course_code)
);

With this rule in place, the database rejects an enrollment for student 150 if no student 150 exists. This protection is called referential integrity. It stops "orphan" records that point at nothing. Some systems have this check off or optional by default. SQLite, for example, leaves foreign key checks off until you run PRAGMA foreign_keys = ON;, so confirm how yours behaves.

Keys are also what joins use to combine tables. The entry on SQL joins shows how.

Common mistakes

• Using a name as a key. Two people can share a name, and a name can change. Use a separate ID.
• Using an email or phone number as a key. People change them, and every table that refers to the old value would need updating.
• Reusing deleted IDs. If an old ID is given to a new row, old references may suddenly point at the wrong thing.
• Leaving a table with no key. Without one, identical rows can pile up, and you cannot point to one row reliably.
• Treating NULL like zero. A NULL grade is "unknown", while zero is a real value. Mixing them changes totals and averages.
• Putting a repeating group in one column. Storing "ART1, MUS2" in a single cell makes searching and counting painful. Use rows in a linked table.

FAQ

Can a table have two primary keys?

No. A table has one primary key, although that key can use several columns. You can add other uniqueness rules to different columns when you need them.

What is the difference between a primary key and a unique column?

Both require distinct values. A primary key also forbids NULL and marks the main identity of the row, and there is only one per table. A table can have many unique columns.

Does a primary key have to be a number?

No, but numbers are the common choice because they are compact and easy to generate. Text values work if they are guaranteed unique and stable.

What is a foreign key for, if I can just type the value?

It lets the database check your typing. Without it, a mistyped student number silently creates a record that points to nobody.

Should every table have a primary key?

Yes, as a rule. PostgreSQL does not force it, but its documentation says it is usually best to give every table one. A row with no identity is also hard to update or delete safely. The database design basics entry shows how keys fit into a whole design. Every entry in the area is listed under Data and Databases.