©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
Editing system-versioned tables in MariaDB

Editing system-versioned tables in MariaDB databases

Edit table System-versioned tables are shown in green color in the table list
Editing system-versioned tables
System-versioned tables are provided by MariaDB from version 10.3.
A third window then appears in the table header with the selection of the table type and the ROW START and ROW END fields, if these have been defined.
Normal tables can be changed to system-versioned tables.

Module for editing system-versioned MySQL tables:

Display information and data of the table.
Create and delete table columns, indexes, partitions and data.
Edit table structure, table options, indexes, partitions, triggers, data and user permissions.
Tools for converting, copying and deleting data and structure.
Backup to the SQL script archive of table and manage files.

The order of the table columns can be changed by drag & drop.
Table head:
Name: Name of the new table
Comment: Comment to be stored for the table.
Next autoindex: First AUTOINCREMENT value for the table.

Engine: Database format of the table.
Collation: Character set and collation of the table.
Row format: Format in which the table rows should be managed in the database.

Table type: Selection of the table type. (BASE TABLE, SYSTEM VERSIONED)
Period start column: Period field ROW START for SYSTEM VERSIONED.
Period end column: Period field ROW END for SYSTEM VERSIONED.
Table columns:
Name: Column name
SV: Select whether an update to the column creates a history data line at SYSTEM VERSIONED.
P/S: Primary or spatial index of the table.
U: Unique indexes to which the table column is linked.
I: Simple indexes to which the table column is linked.
F:Full-text indexes to which the table column is linked.
Type: Data type of the column.
Length/Value: Storage size or format of the data type.
Default value: Default value of the table column that should be inserted if the column is not described when a data record is created.
NULL: column accepts NULL values.
Option: Data type options. For character strings, the character encoding.
Sorting: Collation assignment to character set.
Extra: Extra options for data column (AUTO_INCREMENT, ON UPDATE CURRENT_TIMESTAMP).
Comment: Comment to be stored for the data column.

Create fields after: Number of new columns to be added from the specified position.

Period fields cannot be changed. They must be of type BIGINT or TIMESTAMP.
With older server versions (MariaBD 10.3) these period fields must be created explicitly and selected under Extra: ROW START and ROW END.
Reload table Table is reloaded.
Table information Display table information.
Table data View and edit table data.
Data wizard Data wizard to view and edit data.
History wizard Wizard to display and edit history data of system-versioned tables.
New data record Display form for inserting new data records.
Data import Module to import data into MySQL table.
Table indexes View and edit table indexes.
Foreign keys on table View and edit the foreign keys the table is related to.
Table triggers View and edit triggers on the table.
Table partitions Creation and editing of table partitions..
Table options Editing table options.
User permissions Edit database user permissions to table.
SQL script Output table changes as an SQL script in the SQL editor.
Save changes Save table changes.
Save table definition Save table definition and data in the archive.
Download table Download table definition and data.
Table archive Display table archive.
Table tools Display table tools.
Delete functions Display elete functions.
Table editing menu

Labeled menu by double-clicking title bar.

Save table definition and data to archive:
Backing up tables
Saving the table creation script and data to the MyWAY SQL Manager archive.

Table definition: Table creation script is saved.
Table data: Table data is saved as a SQL script.
GZIP: Files are saved with GZip compression.
Download table definition and data:
Download tables
Download the table creation script and data.

Table definition: Download the table creation script.
Table data: Download the table data.
GZIP: Files are downloaded with GZip compression.
Symbols in table columns:
Primary key Table column in primary index. By clicking, the index is displayed and can be edited.
Spatial key Table column in spatial index. By clicking, the index is displayed and can be edited.
Unique key Table column in unique indexes. By clicking, the indexes will displayed and can be edited.
Index Table column in normal indexes. By clicking, the indexes will displayed and can be edited.
Fulltext index Table column in fulltext indexes. By clicking, the indexes will displayed and can be edited.
In indexes Shows the number of indexes in which the table field exists. By clicking, the indices are displayed and can be edited.
Add table column Add table column to table at position.
Delete table column Delete table column from table.
Functions for selected table columns:
View: Selected columns are shown as data.
Index: Table columns are transferred to a new index.
Unique: Table columns are transferred to a new unique index.
Full text: Table columns are transferred to a new full-text index.
Spatial: Table columns are included in a new spatial index.
Primary key: Table columns are included in a primary index.
Delete: Table columns are deleted.
Reverse the marking: The selection is inverted.
Change system-versioned tables:
Changes to system-versioned tables
In order to be able to add, change or delete data columns in system-versioned tables, the system variable system_versioning_alter_history must be set to KEEP.
System-versioned tables with TIMESTAMP for ROW START and ROW END:
Changes to system-versioned tables
In order to create system-versioned tables with period fields of TIMESTAMP type, it is necessary with older server versions to deactivate the NO_ZERO_DATE setting in the sql_mode system variable. Otherwise the error message appears: Invalid default value for ....
MariaDB System versioned tables, also known as temporal tables, are tables that store historical versions of data and allow data changes to be tracked over time. This type of table was introduced in MariaDB 10.3. They consist of a current data table and a history table. The current data table stores the current version of the data, while the history table maintains the historical versions of the data.

System-versioned tables use timestamp columns to track the validity period of each version. These columns indicate when a particular version of a row became valid and when it was no longer valid. When data is changed in a system-versioned table, MariaDB automatically creates a new historical version of the changed row in the history table. The current data table is updated with the new version and the previous data version remains in the history table. To retrieve historical data, queries can use the FOR SYSTEM_TIME clause, specifying a time period or a specific point in time. This allows the table to be queried as it was at a specific historical point in time.

System-versioned tables support primary keys, unique keys, and foreign keys. Indexes defined on the table are also used to optimize historical data retrieval.

System-versioned tables in MariaDB provide the ability to maintain histories of data changes, enabling querying and analysis over time. They are particularly useful in scenarios where there is a need to track data changes over time, implement audit trails, or perform historical data analysis.
See also: Partitioning of system-versioned tables
More information about system versioned tables can be found here:
Updated: 2023-09-15ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer