Blog

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.

Primary Keys

The  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.

Continue reading “A Bit About Database Keys, Part 2: Primary and Foreign Keys”

Keys are values that uniquely identify a single row in a database table.  They are necessary because it is highly possible that a table will have two rows that contain identical data but represent two separate entities in the real world.

Let’s say we wanted to create a table in PostgreSQL to store information about our friends.  The schema for this table might be created with the following code:

CREATE TABLE friends(
first_name text,
last_name text
);

This table has two text columns, one for first name and one for last name.  Now let’s populate our table with some data:

Continue reading “A Bit About Database Keys, Part 1: Natural vs. Surrogate Keys”