Does UNIQUE automatically add an index?

Question

Or do I have to create index manually?

CREATE TABLE pages(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  stitle TEXT UNIQUE,
  parent INTEGER,
  FOREIGN KEY(parent) REFERENCES pages(id) ON DELETE CASCADE
);

So I should have 2 indexes, right? id and stitle

I would hope so, otherwise it might take longer to verify uniquness. — Jafar Kofahi
– Jafar Kofahi, Commented Aug 6, 2013 at 15:14

Mitch Wheat · Accepted Answer · 2013-08-06 15:16:31Z

4

Creating a UNIQUE constraint automatically creates an index on the columns involved in the UNIQUE constraint.

Ref.

both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database.

.... As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint.

In your example, there would be 2 indexes created.

answered Aug 6, 2013 at 15:16

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Does UNIQUE automatically add an index?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related