Build an index or constraint

Status

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Background

When to use it

Indexes are used to locate data quickly without having to search every row in a table every time the table is accessed.^[1] In DuckDB, an Adaptive Radix Tree (ART) index is mainly used to ensure primary key constraints and to speed up point and very highly selective queries.^[3] In DuckDB, a min-max index, also known as a zonemap, is created automatically for columns of all general-purpose data types.^[3] In DuckDB, indexes slow down loading and updates but speed up certain queries.^[3] In DuckDB, ART indexes must currently fit in memory while the index is being created.^[3] In DuckDB, reach for CREATE INDEX for lookups of single keys or very small slices of a table, not as a general speed-up for analytical scans. (Meridian's view)

Check constraints keep invalid data out at the database level, even when an application that writes to the database would itself accept the invalid data.^[2] A check constraint's predicate, the condition it tests, can evaluate to UNKNOWN because of NULLs, and then the constraint is not violated and the row can be inserted or updated.^[2] Under the SQL standard, a unique constraint does not enforce uniqueness for rows where any of its columns contains a null, though not all relational database management systems (RDBMS) implement the standard here.^[4] Where a column must never repeat and never be missing, declare it NOT NULL as well as UNIQUE, rather than relying on UNIQUE alone. (Meridian's view)

In DuckDB, most ALTER TABLE operations, such as renaming a table or renaming, dropping or retyping columns, are not allowed on a table that has an index, while adding columns and setting or dropping defaults are.^[5] In DuckDB, to rename, drop or retype a column of a table that has an index, the index is dropped, the table altered and the index then recreated.^[5] In DuckDB, adding CHECK and FOREIGN KEY constraints with ALTER TABLE, and the DROP CONSTRAINT clause, are not yet supported.^[5] In DuckDB, settle a table's check and foreign key constraints when the table is created. (Meridian's view) In DuckDB, adding a primary key to an existing table with ALTER TABLE fails if the existing data violates the constraint.^[5] In DuckDB, CREATE INDEX IF NOT EXISTS has no early exit: it attempts to build the index and checks whether it exists only before committing it, so it may run longer than other IF NOT EXISTS statements.^[6]

The method

An index is a copy of selected columns of data from a table, designed for very efficient search, and normally includes a key or direct link to the original row.^[1] Some databases let developers create indexes on column values transformed by functions or expressions, such as an index on upper(last_name).^[1] Many index designs exhibit logarithmic, O(log(N)), lookup performance, and in some applications flat, O(1), performance is possible.^[1] An index may be declared UNIQUE, which creates an implicit constraint on the underlying table.^[1] Database systems usually create an index implicitly on a set of columns declared PRIMARY KEY.^[1]

In DuckDB, a UNIQUE index checks for duplicate values when the index is created and each time data is added, and an insert or update that would create a duplicate raises an error.^[6] In DuckDB, an ART index is created automatically for every primary key or unique constraint defined in a table, to enforce it efficiently.^[7] In DuckDB, primary key and unique constraints differ only in that a table can have one primary key but many unique constraints, and a primary key also enforces that its keys are not NULL.^[7]

A check constraint must be a predicate, which can refer to a single column or to several columns of the table.^[2] A NOT NULL constraint is functionally equivalent to a check constraint with an IS NOT NULL predicate.^[2] Apache Hop's Graph index action creates or drops indexes on the properties of nodes or relationships in a graph database, in the syntax of Neo4j, Memgraph, FalkorDB or Apache AGE.^[8]

Compared with the alternatives

In relational databases, a unique key is a candidate key, one that can uniquely identify the records, other than the one used as the primary key.^[4] In SQL, unique keys have a UNIQUE constraint assigned to them to prevent duplicates.^[4]

Sources

1. [1]Database index - Wikipediasecondary source
2. [2]Check constraint - Wikipediasecondary source
3. [3]Indexes – DuckDBdocumentation
4. [4]Unique key - Wikipediasecondary source
5. [5]ALTER TABLE Statement – DuckDBdocumentation
6. [6]CREATE INDEX Statement – DuckDBdocumentation
7. [7]Constraints – DuckDBdocumentation
8. [8]Graph index | Apache Hopdocumentation