No need to create an index on a column with a UNIQUE constraint, right?

Question

I have a table with three columns.

_id (primary key)    word (unique)     frequency

In SQLite.swift I create the table like this

try db.run( userDictionary.create(ifNotExists: true) {t in
    t.column(wordId, primaryKey: true)
    t.column(word, unique: true)
    t.column(frequency, defaultValue: 1)
    })

I believe the SQLite syntax that would be

CREATE TABLE "words" (
    "_id" INTEGER PRIMARY KEY NOT NULL,
    "word" TEXT UNIQUE NOT NULL,
    "frequency" INTEGER DEFAULT 1
)

I was preparing to add an index on the "word" column to speed up performance since I will have to do frequent queries on it. But then I read the following interesting statement in the Indexes tutorial by TutorialsPoint:

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

So since I already added a unique constraint to the "word" column, there is no need to do anything else. Is that correct? I tried to confirm this in the SQLite documentation but I couldn't find it.

Answer 1

Correct. UNIQUE creates an index. This is how most databases actually enforce it.

Imagine they didn't create an index, what would happen. They would literally need to do a full table scan on every insert and that means performance would go out the window.