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
Alternatively, a PK can be created from the table properties. Select the table, open the "Clusters & Keys" section in the right-hand Properties pane, and click the plus (+) icon to expand a menu of the available constraint types. Select "PK" and choose the columns which comprise the key.
If the table already has a PK, the PK option is hidden from the menu because a table can have only one primary key. Existing key constraints can also be converted to a PK by changing the constraint type in the constraint editor; types that are unavailable for conversion are grayed out in the list.
Keep PK columns in place
By default, columns designated as primary keys are moved to the top of the table's column order, and the diagram displays them in the key section at the top of the table, above the PK line.
To preserve the original column positions instead, enable the Keep PK columns in place option in the table properties.
When the option is enabled, PK columns keep their original position in the inline editor, the Properties panel, the column editor, and the generated DDL. The diagram, however, always displays PK columns in the key section at the top of the table, above the PK line, regardless of this setting.
When "Keep PK columns in place" is enabled, the diagram will display the PK separator as a dashed line instead of solid, providing a subtle visual cue.
With the option enabled, the PK member list in the Properties panel adds an "Order" column, showing the numerical order of the PK columns in the table (1 for the first key column, 2 for the second, and so on).
The setting takes effect immediately in both directions: enabling it preserves the current column positions from that point on, while disabling it immediately moves the PK columns back to the top of the column order.
"Keep PK columns in place" is saved per table and persists with the model. When tables are reverse engineered, the flag is applied automatically in instances where PK columns are not in the first position.
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 to expand a menu of the available constraint types.
Select "Virtual PK" from the menu to open the VPK editor directly, then 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.
See also: