Lesson 7: Keys Part 1: Primary Keys (PK)

Keys are the most fundamental concept in relational databases. They enforce the relationships between tables and ensure data integrity.

What is a Primary Key (PK)?

A Primary Key (PK) is a column (or a set of columns) that uniquely identifies every row in a table. A table can only have one Primary Key.

Two Critical Rules for Primary Keys:

1. Uniqueness (Unicité): No two rows can have the same PK value.
2. Non-Null (Non-Nullité): The PK column cannot contain NULL (missing) values.

The Role of the Primary Key

• Identification: It allows the DBMS to locate any specific record instantly.
• Integrity: It guarantees that every record in the table is distinct.

Choosing a Primary Key

We often use two types of keys:

1. Natural Keys: A real-world attribute that is unique (e.g., a Social Security Number, a ISBN for a book). Caution: Natural keys can sometimes change or be sensitive..
2. Surrogate Keys: An artificial, system-generated identifier (often an auto-incrementing integer) that has no meaning outside the database.

Best Practice: For most modern databases, using Surrogate Keys (simple integers, often auto-incrementing) is preferred because they are stable and fast.

Example (Creating a Table with a PK):

sql CREATE TABLE Customers ( CustomerID INTEGER PRIMARY KEY, -- Defines the PK CustomerName VARCHAR(100) NOT NULL, Email VARCHAR(100) UNIQUE );

In this example, CustomerID must be unique for every customer and cannot be left empty.