Virtual Keys

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.

Use virtual keys to handle data updates and relationships in tables that don't have physical primary or foreign keys.

You can access the settings for configuring virtual keys through two different methods:

1. Properties Editor:

   1. Navigate to the Properties Editor.

   2. Select the Virtual tab from the Tabbed editors.

      

2. Data Editor:

   1. 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.

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.

1. Navigate to the Virtual Unique Key settings.

2. 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.

3. Click OK to save the virtual key.

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.

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.

1. Navigate to the Virtual Foreign Key settings.
2. Click Add to start defining a new foreign key.

3. 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.

4. 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.

To modify or delete existing virtual keys, follow these steps:

1. Access the Configuration Window as detailed in the section Accessing the virtual key configuration window.
2. 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.

To configure how DBeaver handles tables without unique keys, navigate to Window -> Preferences -> Editors -> Data Editor. Here are the options: