Primary Keys and Indexing

Indexing is used to improve speed at runtime, by creating pointers to certain columns in the database. When a query is run, the indexes are used to locate data, avoiding the need to search every row in the given table. This improves query speed, as data is retrieved much more quickly.

Primary key columns are used to uniquely identify each row in a Table. Unless specified otherwise, Pyramid assumes that the first column in each table is the primary key, and creates joins accordingly.

When Auto Index is enabled, Pyramid automatically creates indexes on primary key columns. To do this, Pyramid looks at all the relationships in the model, and creates indexes on the profile key columns in each join.

You can also create indexes and primary keys manually on specified columns, regardless of whether or not Auto Index is enabled. .

• Click here to learn about Auto Indexing.

Create Indexes

To manually add an index to a column, right click on it and select 'Set index' from the context menu:

A column that was manually indexed will appear with an indexing icon (green highlight below). You can remove an index from a column by right clicking and selecting 'Unset index':

Create Primary Keys

In the Tables panel, key columns are indicated by a hash symbol #. You can set a column as a primary key by right clicking and selecting 'Set primary key' from the context menu. You can add indexing to multiple columns in a table.

A column that is manually set as a primary key will appear with a key icon (green highlight). You can remove the primary key by right clicking on the column and selecting 'Unset primary key':

Primary Key Duplicates

By definition, primary key columns contain unique values; each row of a primary key columns contains a unique identifier. If a column that was set as a primary key contains any duplicate rows, you will receive a warning after the model is processed: