Table Structure Diff Tab

Opened from: Object list, Data diff and Text diff tabs
Applicable tab-specific toolbar buttons:
- Jump to the last, next, previous, first change
- Select all, none, invert selection on the left, all, none, invert selection on the right side
- Merge left selected items to the right side, right selected items to the left side
- Delete selected items on the right side, selected items on the right side
- Export to Xlsx or Json
Applicable object types: Tables

This tab allows to compare definition of particular table:

for SQLite, table structure diff tab annotated

Tab is divided into 3 major collapsible sections (Columns, Constraints and Options) and bottom panel for selected item details. Here is explanation of changes highlight annotation on the above screenshot:

1 Changed item ("FirstName" which has changed type size)
2 New item ("Comment")
3 Unchanged column with changed column order ("BirthDate")
4 Changed column with changed column order ("HireDate" has changed nullability)

Vertical toolbar between two panels contains additional tab-specific actions:

Open data diff for the current table
Open data diff for the current table filtered only to new and changed records
opens query result diff with select top 1000 records statement for this table
'Open table definition as text' opens text diff tab with table script generated by application
'Open table definition from sqlite_master' opens text diff tab with table script provided by SQLite metadata

Options section shows STRICT and/or WITHOUT ROWID if any defined for table.

Constraints section includes PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY constraints - any constraint which can be defined on table level (like CREATE TABLE T(A, B, UNIQUE (A, B))), even if it was defined on column level (like CREATE TABLE T(A UNIQUE, B)). Constraints that can be defined only on column level (NOT NULL and DEFAULT) - they are presented in the Columns grid. UI does not show on which level constraint is defined (table or column), but 1) this can be checked using 'Open definition as text' command, and 2) application knows this level and tries to keep it during merge.

Constraints Columns column is either column on which it is defined (for column-level constraints), or it is columns listed explicitely in the table-level constraint definition for PRIMARY KEY, UNIQUE and FOREIGN KEY constraints. For table-level CHECK contraints Columns are not populated - related columns can be found in the CHECK expression in the Definition column.

It's pretty straightforward to identify column or option - whether it is the same or not, and therefore whether it is new, changed or unchanged. Columns and options are identified by their names. But for constraints it is not so trivial, there is no such kind of identifier. Application uses the following rules to identify constraint and understand whether it is new or changed:

To identify PRIMARY KEY only constraint type is used, because table can not have more than one primary key. So, even if primary key have different constraint names or column - it will be considered as changed. PRIMARY KEY considered as new only if other side has no PRIMARY KEY
All other constraints use constraint Type, Name (if defined), Columns (if defined) and constraint order. Constraint having Name is compared with contraint with the same Type and Name. If other side has no constraint with the same Type and Name then it is considered as new, otherwise it is considered as changed or unchanged depending on its definition, whether it is the same or not. Constraint without Name but having Columns populated is compared the same way using Type and Columns. Constraints without Name and Columns are compared just by their order. For example - second table-level CHECK constraint without Name and Columns is compared with second table-level CHECK constraint without Name and Columns on other side, and shown as changed if they have different definitions.

Merge/delete actions use modified version of 12-steps method described in ALTER TABLE article from SQLite documentation. Here are these modifications:

Steps #1, #10 and #12 are used only if foreign keys are enabled for the connection. By default they are enabled, see Enable foreign keys in Settings and PRAGMAs in Open protected files dialog. In this case foreign keys are disabled before the table is re-created, so dropping the old table does not delete or change rows in other tables that reference it (ON DELETE CASCADE, SET NULL, SET DEFAULT). Before commit, PRAGMA foreign_key_check is used for the re-created table and for tables that reference it. If it finds any problem, the merge fails and all changes of this table are rolled back. After commit foreign keys are enabled again.
If the new table has AUTOINCREMENT, then before step #6 the counter of the old table is copied to the new table in sqlite_sequence. Otherwise dropping the old table resets the counter, and ids of rows deleted before the merge could be used again.
If the table has no INTEGER PRIMARY KEY column, then in step #5 rowid is copied with the other columns, so rows keep their rowids. Otherwise the new table numbers rows from 1 and gaps left by deleted rows disappear. It is not copied for WITHOUT ROWID tables and for tables with a real column named rowid.
DROP VIEW statements are executed not in step #9 but before step #7, as suggested in this SQLite forum thread. Otherwise step #7 to rename temporary table fails. The same is done for triggers of other tables and views that refer to the table: they are dropped before step #7 and re-created in step #8. Currently dependent views and triggers are found by the table name as a whole word anywhere in their text, so views using the table in any way are found (for example FROM a, t or main.t), and also views that use such views. As a side effect, an object that only has the same name in its text (for example a column with the name of the table) is also dropped and re-created, without changes.
INSTEAD OF triggers of dependent views are dropped by SQLite together with the views, so they are re-created in step #8 after the views.

Merge/delete actions may fail because of limitations caused by other objects such as foreign keys, existing data, DB engine limitations and so on. For example, new NOT NULL column without DEFAULT constraints can not be merged to the table with records - obvoiusly because these records will have no data in the new column and this will break NOT NULL constraint. Please check merge execution result for details.

If any new column with new constraints is selected without these constraints and then Merge action happens - then this column is merged without these constraints (it is a valid operation). But if new constraint for new column is selected without its column - then it is merged with that new column (otherwise it would be an invalid operation).

Warnings

At the top right corner of each panel, the tab can show a red warning about the table on this side. The same tables are marked with in the Object list, and merge or delete of such table shows a warning in the Execute script dialog.

This can be a shadow table used by "Virtual table name" virtual table. Any changes can be undesirable. - table name starts with the name of a virtual table and an underscore, so it can be a shadow table that stores data of this virtual table. In most cases shadow tables should not be changed by user/application, they should be managed by SQLite virtual tables.
Some parts of table definition could not be recognized (including comments). They can not be included in the merge script. - table definition has parts that the application's CREATE TABLE parser could not recognize, for example syntax that SQLite accepts but does not document, or it has comments. "(including comments)" is shown only when the definition has comments.
The detailed table definition could not be read. Only basic column information is available. - the parser failed on this table definition, so columns are read by PRAGMA table_info. It returns only column name, type, NOT NULL, DEFAULT and primary key, so other constraints, COLLATE, AUTOINCREMENT and generated columns are not available.

Merge/delete actions build the script from the table definition shown in this tab, not from the original CREATE TABLE text. So comments and the parts that the application could not read are not included in the script, and when the table is re-created, they are lost in the target table. To check the original text of an incomplete table, use 'Open table definition from sqlite_master' and compare it with 'Open table definition as text', which shows the definition read by the application.

Last updated: 2026-09-25