PRIMARY KEY

A PRIMARY KEY is the column (or combination of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it cannot be NULL.

Why Primary Keys Matter

Think of it like a student ID number. There might be two students named "Musa" in your school, but each one has a unique ID. The primary key is that unique ID for every row in your table.

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO users VALUES (1, 'musa123', 'musa@email.com');
INSERT INTO users VALUES (2, 'fatima_dev', 'fatima@email.com');

-- This will fail because user_id 1 already exists:
INSERT INTO users VALUES (1, 'john_doe', 'john@email.com');

-- This will fail because PRIMARY KEY cannot be NULL:
INSERT INTO users VALUES (NULL, 'test', 'test@email.com');

AUTOINCREMENT

Instead of manually assigning IDs, let the database do it:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_name TEXT NOT NULL,
  total REAL NOT NULL
);

INSERT INTO orders (customer_name, total) VALUES ('Bilal', 150.00);
INSERT INTO orders (customer_name, total) VALUES ('Amina', 275.50);
INSERT INTO orders (customer_name, total) VALUES ('Musa', 99.99);

SELECT * FROM orders;

Notice I did not specify order_id. SQLite automatically assigned 1, 2, 3. AUTOINCREMENT guarantees that IDs are never reused, even if rows are deleted.

Composite Primary Keys

Sometimes one column is not enough. You can use multiple columns together as a primary key:

CREATE TABLE course_enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrollment_date TEXT,
  PRIMARY KEY (student_id, course_id)
);

-- Each student can enroll in a course only once
INSERT INTO course_enrollments VALUES (1, 1, '2026-01-15');
INSERT INTO course_enrollments VALUES (1, 2, '2026-01-16');

-- This will fail because student 1 already enrolled in course 1:
INSERT INTO course_enrollments VALUES (1, 1, '2026-02-01');

Composite keys are common in junction tables (like enrollments, which links students to courses).