When you define constraints in SQL Server, on which key type does the engine automatically create a unique index by default (often clustered unless specified otherwise)?

Introduction / Context:Indexes often accompany constraints in SQL Server to enforce uniqueness and performance. Understanding which constraints auto-create indexes helps with design and troubleshooting.

Given Data / Assumptions:

• A table has constraints such as PRIMARY KEY and FOREIGN KEY.
• We want to know which constraints automatically create indexes.
• Default behaviors may be overridden (e.g., nonclustered primary key).

Concept / Approach:

Creating a PRIMARY KEY constraint automatically creates a unique index on the key columns (clustered by default if the table does not already have a clustered index). FOREIGN KEY constraints do not automatically create an index, though adding one is typically recommended for referential integrity checks and join performance.

Step-by-Step Solution:

Verification / Alternative check:

Script out a table with a primary key; SQL Server shows a corresponding unique index. Adding a foreign key does not show a new index unless explicitly created.

Why Other Options Are Wrong:

Foreign key only: false. Both primary and foreign keys: overstates behavior. Never automatically: contradicts primary key behavior.

Common Pitfalls:

Forgetting to index foreign keys, which can slow deletes/updates on the referenced table and joins. Assuming the primary key must be clustered—this is configurable.

Final Answer:

primary keys only.