MySQL语法中为何不允许单表存在两个独立主键？
Great question—this is one of those things that feels counterintuitive at first, especially since a UNIQUE NOT NULL column seems to do the exact same "unique identifier" job as a primary key. Let’s unpack the reasons clearly:
1. The Core Definition of a Primary Key (Per SQL Standards)
First, let’s go back to what a primary key is by definition. The SQL standard specifies that a primary key is the minimal set of columns that uniquely identifies every row in a table, and crucially, it’s meant to be the single, official unique identifier for the table.
Think of it like a person’s government-issued ID: you might have other unique identifiers (like a passport number or email), but there’s only one official ID recognized as the primary way to confirm your identity. A table’s primary key serves the same role—database systems, ORMs, and developers rely on it as the definitive way to reference a row. Allowing two primary keys would break this single-source-of-truth semantic: which one is the "real" identifier?
2. Underlying Database Engine Dependencies
For engines like InnoDB (MySQL’s default), the primary key does more than just enforce uniqueness—it’s the foundation of the clustered index. This index determines how data is physically stored on disk: rows are ordered and stored alongside the primary key index.
If you had two separate primary keys, the engine would have no way to decide which one to use for organizing physical storage. This would lead to conflicting storage structures, degraded query performance, and potential data consistency issues.
In contrast, a UNIQUE NOT NULL column creates a secondary unique index—this is a separate structure that just enforces uniqueness, but it doesn’t dictate the physical storage order of the table. That’s why multiple such columns are allowed.
3. Avoiding Ambiguity in Data Modeling & Tooling
Beyond technical constraints, having a single primary key keeps your data model clear and avoids confusion for anyone working with the table. For example:
- When creating foreign key relationships, you’d have to choose which primary key to reference—introducing unnecessary complexity.
- ORM tools (like Hibernate or Django ORM) are designed to work with a single primary key per table; supporting multiple would require extra configuration and could lead to bugs.
- Debugging and maintaining the database becomes harder when there’s no single, agreed-upon identifier for rows.
Why UNIQUE NOT NULL Is Allowed (Even Though It Acts Like a Primary Key)
You’re right that a UNIQUE NOT NULL column can uniquely identify rows—but it’s important to distinguish between business constraints and logical identity. A primary key defines the logical identity of a row, while a UNIQUE NOT NULL column enforces a business rule (e.g., "no two users can have the same email").
MySQL lets you have multiple business-level unique constraints because they serve different purposes, but it enforces a single primary key to maintain the logical consistency and structural integrity of the table.
内容的提问来源于stack exchange，提问作者Abhishek Jha
