4. Unique Indexes

Indexes can also be used to enforce uniqueness of a column's value, or the uniqueness of the combined values of more than one column.

Valentina DB can make UNIQUE both default and hash indexes.

When an index is declared unique, multiple table rows with equal indexed values are not allowed. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

Valentina DB automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

Generally speaking, SQL Standard and Relational model have PK, FK, UNIQUE constraints, but there is no indexes at all. Indexes can be considered as optimization of DBMS. Nevertheless indexes are very fundamental feature of any DB. For example, UNIQUE constraint cannot be implemented effectively without index.

So we suggesting to consider UNIQUE constraint and UNIQUE index as two sides of the same coin.

API

Use 'fIndexed' + 'fUnique' flags of VField

fld = tbl.CreateVarCharField( "fld_name", 2044, fIndexed + fUnique )

to change existed index to/from Unique

fld.IsUnque = true

SQL

In SQL you can define indexing of a field(s) in few ways.

In CREATE TABLE/ ALTER TABLE when you define a field. See also CREATE TABLE

CREATE TABLE T1( 
    f1 LONG INDEXED UNIQUE 
)

In CREATE TABLE/ ALTER TABLE using constraint UNIQUE

CREATE TABLE T1( 
    f1 LONG 
    CONSTRAINT cname UNIQUE (f1)
)

Using CREATE INDEX command.

CREATE UNIQUE INDEX index_name ON TABLE_NAME( COLUMN, ... )

To drop it you can use DROP IDEX or ALTER TABLE drop constraint form. You should drop that object, which you did create before.

Table of Contents

4. Unique Indexes

API

SQL