In Part 1 of this series about database keys, we looked at the difference between natural and surrogate keys, using a table with data about friends. Here’s that table again:
SELECT * FROM friends; first_name | last_name | hair_color | id ------------+-----------+------------+---- Monica | Geller | | 1 Ross | Geller | | 2 Chandler | Bing | | 3 Joey | Tribbiani | | 4 Rachel | Green | | 5 Phoebe | Buffet | blonde | 6 Monica | Lewinsky | | 7 Al | Green | | 8 Joey | Kangaroo | orange | 9 (9 rows)
The table contains columns for data about first name, last name, and hair color, plus an
id column that was created to uniquely identify each row, regardless of whether or not the values in the other columns were unique.
id column of the
friends table is an example of a primary key. A primary key is used to uniquely identify a row in the same table. Columns can be explicitly specified as primary keys in PostgreSQL using
PRIMARY KEY , as was done when the column was created in the previous article:
ALTER TABLE friends ADD COLUMN id serial PRIMARY KEY;
A primary key is usually, though not always, an automatically-incrementing integer. But no matter the data type, a primary key must be unique, and so surrogate keys make great primary keys.