Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
CREATE INDEX
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The CREATE INDEX statement allows for creating indexes on table columns.
CREATE INDEX
In its simplest form, the statement can be used like this:
// Create an index on a single column
create.createIndex("index").on("table", "column").execute();
// Create an index on several columns
create.createIndex("index").on("table", "column1", "column2").execute();
CREATE UNIQUE INDEX
In many dialects, there is a possibility of creating a unique index, which acts like a constraint (see ALTER TABLE or CREATE TABLE), but is not really a constraint. Most dialects will create an index automatically to enforce a UNIQUE constraint, so using a constraint instead may seem a bit cleaner. A UNIQUE INDEX is created like this:
// Create an index on a single column
create.createUniqueIndex("index").on("table", "column").execute();
// Create an index on several columns
create.createUniqueIndex("index").on("table", "column1", "column2").execute();
Sorted indexes
In most dialects, indexes have their columns sorted ascendingly by default. If you wish to create an index with a differing sort order, you can do so by providing the order explicitly:
// Create a sorted index on several columns
create.createIndex("index").on(
table(name("table")),
field(name("column1")).asc(),
field(name("column2")).desc()
).execute();
Partial indexes (with WHERE clause)
A few dialects support a WHERE clause when creating an index. This is very useful to drastically reduce the size of an index, and thus index maintenance, if only parts of the data of a column need to be included in the index.
// Create a partial index
create.createIndex("index").on("table", "column").where(field(name("column")).gt(0)).execute();
Feedback
Do you have any feedback about this page? We'd love to hear it!