Table structure diff tab

Opened from: Object list, Data diff and Text diff tabs
Applicable tab-specific toolbar actions:
- Export to Excel
- Jump to the next, previous 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
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 dable 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 & #12 to disable and then re-enable foreign key are not used. These options should be set on each connection, the whole merge script is executed in scope of the new connection - therefore foreign keys were not explicitely enabled before and there is no need to disable them.
By the same reason step #10 (If foreign key constraints were originally enabled..) is skipped as well, but that's the questionable point. Application has no knowledge on whether these keys are used or not. Probably that's the subject of further improvements and needs to be configured.
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.

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

Free version limitations:

The whole tab is not available

Last updated: 2023-10-05