In SQL Server, does the system automatically create an index for every primary key and for every foreign key, or only guarantee automatic indexing for primary keys?

Introduction / Context:
Keys and indexes are related but distinct. Primary keys enforce uniqueness and are automatically backed by a unique index. Foreign keys enforce referential integrity, but SQL Server does not automatically create an index for them.

Given Data / Assumptions:

• Primary key defined via PRIMARY KEY constraint.
• Foreign key defined via FOREIGN KEY constraint.
• No explicit CREATE INDEX statements provided.

Concept / Approach:
When you add a PRIMARY KEY constraint, SQL Server creates a unique index (clustered by default if not otherwise specified). For FOREIGN KEY constraints, indexing is recommended for join and delete/update performance, but it is not automatic; you must create a supporting index explicitly.

Step-by-Step Solution:
Create table with PK: PK yields unique index (viewable in sys.indexes).Add FK referencing parent table; no index appears automatically on the FK column(s).Manually add: CREATE INDEX IX_Child_FK ON dbo.Child(ParentID);

Verification / Alternative check:
Query sys.indexes after adding PK and FK to observe index presence/absence. Execution plans for joins show benefits of indexing foreign keys.

Why Other Options Are Wrong:
Automatic indexes on FKs do not exist. Recovery model has nothing to do with automatic key indexing. Saying none are auto-indexed ignores PK behavior.

Common Pitfalls:
Assuming FKs are indexed; suffering slow deletes/updates due to missing FK indexes.

Final Answer:
Automatic index for primary keys; foreign keys are not auto-indexed