6

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.

Suragch
  • 484,302
  • 314
  • 1,365
  • 1,393
  • 3
    [Documentation](http://www.sqlite.org/fileformat2.html#sqlite_master): "UNIQUE and PRIMARY KEY constraints on tables cause SQLite to create internal indexes". – CL. Apr 27 '16 at 07:09
  • @CL. "*internal indexes*" is why `PRIMARY KEY` and `CREATE INDEX` show up as `index` -type entries in `sqlite_master`, while `UNIQUE` -constraints do not? – user4157124 Mar 12 '18 at 00:44
  • @user4157124 Indexes created for UNIQUE constraints show up in `sqlite_master`, too. – CL. Mar 12 '18 at 08:36

1 Answers1

12

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.

Harry
  • 11,298
  • 1
  • 29
  • 43