Indexes and Keys
Use the Indexes & Keys view to change a table's indexes, foreign keys, and primary key.
Changing the Primary Key #
Multiple columns can be set as the primary key of a table.
-
Check the box beside each column that should be part of the primary key.
-
To index only a prefix of a column, enter a length in its Length column.
Auto Increment (MySQL) #
Below the primary key, Auto Increment Column chooses which column MySQL numbers automatically, and Auto Increment Value shows the next number it will use.
| Choose | What happens |
|---|---|
| None | Turns auto-increment off. Existing values stay. |
| A column | Makes that column the table's only primary key column and turns on auto-increment for it. |
| New Column… | Adds a new column (Name, Type, and Starting at) as the auto-incrementing primary key, replacing the current primary key. |
Click Reset… to set the next value, such as after deleting test rows. MySQL won't set it lower than the highest value already in the column.
Creating an Index #
-
Select the table in the sidebar.
-
Click the add button under the Indexes list.
-
Enter a name.
-
Choose the index Kind and Type.
-
Double-click the "Columns" column to enter which columns are part of the index. (See below.)
-
Click outside of the column list editor to commit it, Press Return to accept the list, or Escape to cancel.
The column list is an editable SQL list of the columns or expressions to index You can add columns to it two ways:
-
Type the column names. Suggestions appear as you type.
-
Or, click or double-click a column in the list to insert it at the insertion point (or the end, if you haven't clicked in the text).
The order matters. In a multi-column index, the columns are indexed in the order you list them, so last_name, first_name is not the same index as first_name, last_name.
The syntax depends on the database:
| Database | Syntax |
|---|---|
| MySQL | column, column(prefix), (expression) |
| PostgreSQL | column, (expression) |
| SQLite | column, (expression) |
- A plain name indexes that whole column.
- In MySQL, a number in parentheses after a column name, such as
title(20), indexes only that many leading characters. - An expression in its own parentheses, such as
(lower(email)), indexes the result of the expression instead of a column.
A column name that needs quoting because of spaces or other characters is quoted for you when you insert it from the list. If the list can't be understood, Querious beeps and keeps the field open so you can correct it.
Foreign Keys #
To create a foreign key
-
Select the table in the sidebar.
-
Click the add button under the Foreign Keys list.
-
Optionally name the key. If you leave it blank, the server will create a default name.
-
Double-click the Columns, Foreign Table, or Foreign Columns cell to open the columns editor.
-
Choose the Foreign Table the key references.
-
The list shows this table's columns. For each one you want in the key, choose the matching foreign column from the popup beside it.
