# CREATE INDEX

CREATE INDEX defines an index over one or more columns or expressions. A UNIQUE index also rejects duplicate non-`NULL` keys. A partial index includes only rows satisfying its `WHERE` condition.

Existing rows are validated when an index is created. An invalid unique index definition therefore fails if its current rows already contain duplicate keys.

## Syntax

```text
CREATE [UNIQUE] INDEX [IF NOT EXISTS] name
ON table (expression [COLLATE collation] [ASC | DESC], ...)
[WHERE condition]
```

## Examples

### A case-insensitive unique email

Indexing `lower(email)` makes uniqueness apply to the normalized expression. The partial predicate excludes missing addresses:

```sql
CREATE TABLE contacts(id INTEGER PRIMARY KEY, email TEXT);
CREATE UNIQUE INDEX contact_email
ON contacts(lower(email))
WHERE email IS NOT NULL;
INSERT INTO contacts VALUES (1, 'Ada@example.org'), (2, NULL);
```

```sql
SELECT id
FROM contacts
WHERE lower(email) = 'ada@example.org'
FORMAT csv;
```

```text
1
```

Inserting another non-`NULL` email whose lowercase form is `ada@example.org` violates the unique index.

### A compound index

```sql
CREATE TABLE orders(id INTEGER PRIMARY KEY, account_id UInt32, day Date);
CREATE INDEX orders_by_account_day ON orders(account_id, day DESC);
```

The index can support queries constrained by `account_id` and ordered or filtered by `day`. Use [`EXPLAIN QUERY PLAN`](https://coordinatordb.com/docs/reference/statements/explain) to inspect the selected access path for a concrete query.

Remove an index with [`DROP INDEX`](https://coordinatordb.com/docs/reference/statements/drop). Each index must also be maintained when its table changes, so choose indexes for the queries the application actually runs.