Primary keys

A primary key is a constraint that uniquely identifies each record in a table.

Primary keys (PK) must contain UNIQUE and not NULL values.

A table can have only ONE primary key, although it can consist of multiple columns.

SqlDBM supports physical as well as virtual primary keys (VPK). Physical PKs conform to database syntax and are read and written to/from the database during Reverse and Forward Engineering. VPKs are metadata objects which are visible on the diagrams but are not written back to the database.

Most database providers support primary keys for physical tables. Although some databases (e.g., Snowflake) do not enforce them, defining primary keys is still a best practice for purposes of clarity and maintenance.

Primary keys can be applied or removed from existing tables at any time. However, doing so in existing tables that contain data may result in a database error.

Declaring physical primary keys

On your diagram, select the table you wish to edit.

Highlight a column that you would like to designate as a primary key. Select multiple columns using ctrl+Click (PC) or ⌘ cmd+Click on (Mac).

Drag the column(s) to the top of the table to designate them as primary keys.

SqlDBM will automatically create the corresponding DDL behind the scenes.

Figure 1. Create a primary key

Create a Virtual Primary Key (VPK)

VPKs are created from the table properties. Select any table, open the "Clusters & Keys" section in the right-hand Properties pane, and click the plus (+) icon.

On the next screen, change the Key type to "Virtual PK", provide a name, and select the columns which comprise the VPK.

Note that VPKs can be created in bulk using the Suggested Relationships wizard.

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.