Virtual Keys
Table of contents
Overview
The virtual key feature in DBeaver allows you to save changes to tables that do not have a primary key or unique index. You can define a virtual key, which is a set of columns that form a unique combination of values. This ensures each row can be uniquely identified, allowing you to save changes to the table.
Note: Virtual keys exist only in DBeaver, not in the database itself. You can define these keys even if your database does not support them.
Tip: All virtual entities are stored in the
data-sources.json
file. For more information, refer to the Configuration Files article.
Virtual key management
Use virtual keys to handle data updates and relationships in tables that don't have physical primary or foreign keys.
Accessing the virtual key configuration window
You can access the settings for configuring virtual keys through two different methods:
-
- Navigate to the Properties Editor.
Select the Virtual tab from the Tabbed editors.
-
Right-click in the Data Editor.
- Select Logical structure to view different options for virtual entities.
- You can either choose a specific option from the list or click on Edit to open the configuration window where you can manage virtual entities.
Note: When creating a connection in the Connection Details, ensure the checkbox for Show virtual model Editor is selected to access virtual key features.
Creating a virtual unique key
Inserting new rows into a table that do not have a primary key or unique index can generally be completed without issues, but updating existing data can be tricky. A virtual primary key helps you update data by acting as a unique identifier for rows in tables without a physical primary key.
- Navigate to the Virtual Unique Key settings.
Configure the options:
Field/Checkbox Description Table Automatically filled with the name of the table being modified. Name Enter a name for the virtual key. Use All Columns Check this to include all columns from the table in the virtual unique key. Columns List Displays columns available for selection. Select All Button to quickly select all columns in the Columns List. Important: Be careful with the Use All Columns option. It may cause changes in multiple rows if the columns do not uniquely identify each row.
Click OK to save the virtual key.
Error handling during data modification
If you begin modifying data in a table that lacks a primary or unique key, DBeaver will alert you with the following error message:
You can handle the error in one of two ways:
Use All Columns: Selecting this option immediately treats all columns as a virtual key.
Important: Use this option carefully, as it may cause changes in multiple rows if the columns do not uniquely identify each row.
Custom Unique Key: Alternatively, you can create a more specific virtual unique key by choosing Custom Unique Key. For more information on creating a virtual unique key, refer to the Creating a virtual unique key section.
Creating a virtual foreign key
Virtual foreign keys are useful in databases where physical foreign key constraints are not present. They allow you to define and enforce relationships between tables virtually, ensuring data integrity.
- Navigate to the Virtual Foreign Key settings.
- Click Add to start defining a new foreign key.
Define the foreign key details:
Field/Option Description Table Automatically displays the name of the current table. Reference table container Select the schema of the reference table. Reference Table Choose a table to reference. Selecting a table updates the Unique key and Columns fields. Unique key Displays keys available in the reference table. Select one that the foreign key will link to. Columns Customize which columns in your table map to columns in the reference table. Options in this dropdown are based on the selected unique key. Select <new>
to create a new mapping or choose existing columns to modify their mapping.Click OK to create the foreign key.
Tip: You can also create virtual foreign keys in the ER Diagrams by dragging and dropping columns. This shows the foreign key connections between tables visually, making it easier to see how your database is structured.
Editing virtual keys
To modify or delete existing virtual keys, follow these steps:
- Access the Configuration Window as detailed in the section Accessing the virtual key configuration window.
- Choose the virtual key you wish to modify.
- To make changes, select Edit and adjust as needed.
- To delete, choose Remove.
Note: Virtual foreign keys cannot be edited once created; they can only be removed if changes are needed.
Settings for missing keys
To configure how DBeaver handles tables without unique keys, navigate to Window -> Preferences -> Editors -> Data Editor. Here are the options:
Option | Description |
---|---|
Use all table columns as a key upon saving | Utilizes all columns as a unique key. |
Disable data editing | Prevents editing on tables without a unique key. |