urQL/docs/05-ddl-index.md

# DDL: Index
*supported in urQL parser, not yet supported in Obelisk*

## CREATE INDEX

This command creates an index over selected columns of an existing table.

### AST
```
<create-index> ::=
  CREATE [ UNIQUE ] INDEX <index>
    ON [ <db-qualifer> ] <table>
    ( <column> [ ASC | DESC ] [ ,...n ] )
    [ <as-of-time> ]
```

### Examples
```
CREATE INDEX ix-vendor-id ON product-vendor (vendor-id);
CREATE UNIQUE INDEX ix-vendor-id2 ON dbo.product-vendor
  (vendor-id DESC, name ASC, address DESC);
CREATE INDEX ix-vendor-id3 ON purchasing..product-vendor (vendor-id);
```

### API

```
+$  create-index
  $:
    %create-index
    name=@t
    object-name=qualified-object
    is-unique=?
    columns=(list ordered-column)
  ==
```

### Arguments

**`UNIQUE`**
Specifies that no two rows are permitted to have the same index key value.

**`<index>`**
User-defined name for the new index. This name must follow the Hoon term naming standard. Index names are unique within tables.

**`[ <db-qualifer> ] <table>`**
Name of existing table the index targets.
If not explicitly qualified, defaults to the Obelisk agent's current database and 'dbo' namespace.

**`<column> [ ASC | DESC ] [ ,...n ] `**
List of column names in the target table. This list represents the sort hierarchy and optionally specifies the sort direction for each level. The default sorting is `ASC` (ascending).

**`<as-of-time>`**
Timestamp of index creation. Defaults to `NOW` (current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps. 

### Remarks

This command mutates the state of the Obelisk agent.

### Produced Metadata

Schema timestamp

### Exceptions

index must be created by local agent
index name already exists for table
database `<database>` does not exist
table does not exist
column does not exist
create index `<index>` as-of schema time out of order
create index `<index>`as-of data time out of order
UNIQUE specified and existing values are not unique for the column(s) specified
create index state change after query in script

### Example

*missing*

## ALTER INDEX

*supported in urQL parser, not yet supported in Obelisk*

Modifies the structure of an existing `<index>` on a user `<table>` or `<view>`.

```
<alter-index> ::=
  ALTER [ UNIQUE ] INDEX <index>
    ON [ <db-qualifer> ] <table>
    [ ( <column> [ ASC | DESC ] [ ,...n ] ) ]
    { DISABLE | RESUME}
    [ <as-of-time> ]
```

### API
```
+$  alter-index
  $:
    %alter-index
    name=qualified-object
    object=qualified-object
    columns=(list ordered-column)
    action=index-action
  ==
```

### Arguments

**`UNIQUE`**
Specifies that no two rows are permitted to have the same index key value.

**`<index>`**
Specifies the target index.

**`[ <db-qualifer> ] <table>`**
Name of the underlying object of the index.

**`<column> [ ASC | DESC ] [ ,...n ] `**
List of column names in the target table. This list represents the sort hierarchy and optionally specifies the sort direction for each level. The default sorting is `ASC` (ascending).

**`DISABLE | RESUME`**
Used to disable an active index or resume a disabled index.

**`<as-of-time>`**
Timestamp of index alteration. Defaults to `NOW` (current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps.

### Remarks

This command mutates the state of the Obelisk agent.

Cannot alter primary key and foreign key indices.

`RESUME` will rebuild the index if the underlying object is dirty.

### Produced Metadata

Schema timestamp

### Exceptions

index must be altered by local agent
index name does not exist for table
database `<database>` does not exist
table does not exist
column does not exist
alter index `<index>` as-of schema time out of order
alter index `<index>`as-of data time out of order
UNIQUE specified and existing values are not unique for the column(s) specified
alter index state change after query in script

### Example

*missing*

## DROP INDEX

*supported in urQL parser, not yet supported in Obelisk*

Deletes an existing `<index>`.

```
<drop-index> ::= 
  DROP INDEX <index>
    ON [ <db-qualifer> ] <table>
    [ <as-of-time> ]
```

### API
```
+$  drop-index
  $:
    %drop-index
    name=@tas
    object=qualified-object
  ==
```

### Arguments

**`<index>`**
The name of the index to delete.

**`[ <db-qualifer> ] <table>`**
`<table>` or `<view>` with the named index.

**`<as-of-time>`**
Timestamp of dropping index. Defaults to `NOW` (current time). When specified, the timestamp must be greater than both the latest database schema and content timestamps.

### Remarks

This command mutates the state of the Obelisk agent.

This command can be used to delete a `<foreign-key>`.

If `<view>` is shadowing `<table>`, the system attempts to find `<index>` on `<view>` first, then `<table>`.

### Produced Metadata

Schema timestamp

### Exceptions

index must be dropped by local agent
index name does not exist for table
database `<database>` does not exist
table does not exist
drop index `<index>` as-of schema time out of order
drop index `<index>`as-of data time out of order
drop index state change after query in script

### Example

*missing*