Python SQL PRIMARY KEY: Entity Uniqueness & Clustering
A `PRIMARY KEY` uniquely identifies each row in a database table. It inherently combines `NOT NULL` and `UNIQUE` constraints. A table can have at most ONE primary key, which can consist of a single column (surrogate ID) or multiple columns (composite key). In many engines (MySQL InnoDB, SQL Server), the primary key defines the physical clustered storage order of table data on disk.
"A `PRIMARY KEY` is a social security number or fingerprint for a database row: no two rows can share it, and it cannot be blank."
Deep Dive: How It Works
Clustered Index: In clustered storage engines, row data is physically organized on disk in order of the Primary Key B-Tree.
Composite Primary Keys: `PRIMARY KEY (order_id, item_id)` uniquely identifies items within an order without needing an artificial surrogate key.
Natural vs Surrogate Keys: Surrogate keys (auto-increment integers or UUIDs) remain stable even if business fields change.
Syntax Blueprint
-- Single Column PK CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); -- Composite PK CREATE TABLE order_items ( order_id INT, product_id INT, PRIMARY KEY (order_id, product_id) );
Declares single-column or composite primary key structures.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Using volatile natural data (like email addresses or phone numbers) as primary keys.Why it happens: If a user changes their email, cascading foreign key updates across dozens of tables is expensive and risky.
How to fix: Use immutable surrogate keys (integers, UUIDs) as primary keys; apply UNIQUE to emails.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Define Course Enrollments with Composite PK
- Create table `course_enrollments` with columns `student_id INTEGER NOT NULL`, `course_id INTEGER NOT NULL`, `grade TEXT`, and composite primary key `PRIMARY KEY(student_id, course_id)`.
- Insert record (1, 100, "A") and select it.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.