唯一索引与主键索引
了解 PostgreSQL 如何自动使用索引支持主键和唯一约束，以及如何高效利用唯一索引保证数据完整性。
唯一索引与主键索引 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Constraints Need Indexes
To enforce uniqueness, PostgreSQL must check existing rows fast — so it auto-builds a unique index behind every unique constraint and primary key.
Primary Key Index
Declaring a primary key creates a unique B-tree index on those columns and marks them NOT NULL. The code shows it in action.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT
);The Hidden Index
That primary key silently builds an index called users_pkey. You never wrote CREATE INDEX, yet lookups by id are already fast.
Unique Constraints
A UNIQUE constraint also creates a supporting index and rejects duplicate values — the ALTER TABLE below adds one.
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);Standalone Unique Index
You can build a unique index directly, without a named constraint — it still enforces uniqueness, as the code shows.
CREATE UNIQUE INDEX idx_users_email ON users (email);Multi-Column Uniqueness
Index multiple columns together for multi-column uniqueness — like one membership per user per group. The code makes the pair unique.
CREATE UNIQUE INDEX idx_membership ON memberships (user_id, group_id);NULLs and Uniqueness
PostgreSQL treats NULLs as distinct by default, so a unique column allows many NULLs. Add NULLS NOT DISTINCT to forbid more than one.
CREATE UNIQUE INDEX idx_code ON items (code) NULLS NOT DISTINCT;Partial Unique Index
A partial unique index enforces uniqueness only on rows matching a WHERE clause — for example, one active session per user.
CREATE UNIQUE INDEX idx_active ON sessions (user_id) WHERE active;Dual Benefit
A unique index gives you two things at once: data integrity and fast lookups. The planner uses it like any other B-tree index.
Constraint vs Index
The constraint is the logical rule; the index enforces it. You can't drop the index without the constraint, and foreign keys can reference these columns.
Cost Awareness
Unique indexes add write overhead since every insert checks for duplicates. Index only the columns that genuinely need to be unique.
Quick Check
Primary keys, NULLs, partial indexes — let's see what stuck about unique indexes.
Recap
Recap: every primary key and unique constraint is backed by a unique B-tree index for integrity plus speed. You can also go standalone, multi-column, or partial — but each adds write cost.
用 AI 导师学习 Advanced PostgreSQL: Indexing, Partitioning, Replication — 免费
在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。
- 课程
- 11
- 课程
- 44
常见问题解答
「唯一索引与主键索引」课时是免费的吗？
是的 — 「唯一索引与主键索引」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程的其余内容，请升级到 CoddyKit PRO。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
「唯一索引与主键索引」这节课中我会学到什么？
了解 PostgreSQL 如何自动使用索引支持主键和唯一约束，以及如何高效利用唯一索引保证数据完整性。 你通过在浏览器中直接运行的动手代码来练习 Advanced PostgreSQL: Indexing, Partitioning, Replication，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Advanced PostgreSQL: Indexing, Partitioning, Replication 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「唯一索引与主键索引」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课中编写并运行代码吗？
能。每节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。