Primary Keys

Primary Keys

SQL tables sometimes have a column that uniquely identifies each row of that table. These special columns are called primary keys.

A primary key column has a few requirements:

  • None of the values can be NULL.
  • Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
  • A table can not have more than one primary key column.

Here’s an orders table where the order_id is its primary key:

order_id customer_id total_cost purchase_date
1 1001 13.99 2022-01-01
2 1294 61.42 2022-01-01
3 1001 23.45 2022-01-02

Syntax

PRIMARY KEY columns can be used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation which will not allow you to insert the new row.

The statement below sets a PRIMARY KEY on the students table:

Loading...

Foreign Keys

When the primary key for one table appears in a different table, it is called a foreign key.

Why is this important? The most common types of joins will be joining a foreign key from one table with the primary key from another table. For instance, when we join the orders table and the customers table, we join on the customer_id column, which is a foreign key in orders and the primary key in customers.

Interested in helping build Docs? Read the Contribution Guide or share your feedback.

Learn SQL on Codecademy