Alternate Keys

Alternate keys and how to create them

SqlDBM Support avatar
Written by SqlDBM Support
Updated over a week ago

An alternate (UNIQUE) key is a column(s) in a table that uniquely identifies every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called Alternate Keys.

The DDL for alternate keys varies between databases but SqlDBM makes this transparent. When you create an alternate key in a diagram, the requisite DDL will automatically be generated according to the database type of your project.

Creating an alternate key

To create an alternate key, select the desired table and go to the "Indexes and Keys" section of the table properties on the right panel.

Press the "+" icon to create an alternate key.

Make sure the "Unique" is selected from the "Type" dropdown.

Specify the alternate key name and properties, selecting the columns that will form the key.

Toggle alternate key creation on and off by selecting the "Generate" option.

Review existing alternate keys

Select the desired table and go to the "Indexes and Keys" section of the table properties on the right panel.

Click on the index or alternate key name to review its properties.

Note, alternate keys are created with the prefix "ak_" by default.

RELY property (Snowflake)

Snowflake constraints (PK, FK, AK) now support RELY in Reverse/Forward Engineering and object properties. The RELY property is a keyword included in the constraint definition and acts as a hint for the Snowflake optimizer to rely on the integrity of the constraint and perform join elimination on views and redundant queries.

Set or unset RELY by clicking on any constraint from the Keys section of the table properties and selecting the RELY option.

For FKs, clicking on the relationship line will bring up this menu directly.

An article describing how RELY can improve query cost and performance is available here.

See also:

Did this answer your question?