Applying the changes after editing a table

While you edit a table's structure, nothing is written to the database until you apply your changes. When you are ready, click Apply to commit them, or Cancel to discard them.

How SQLite Expert applies changes

There are two ways a change can be applied, and SQLite Expert chooses between them for you.

Directly. Where SQLite can express your edit as ALTER TABLE statements, that is what runs — renaming columns, adding and removing them, renaming the table, and combinations of those. Nothing is copied and the table's stored definition keeps the formatting you gave it.

By restructuring. Some changes have no direct form: reordering columns, changing a column's type, most changes to constraints, or removing a column something else still refers to. For those, SQLite Expert:

1. renames the existing table to a temporary name;
2. creates a new table with your new structure and the original name;
3. copies the data across from the old table;
4. deletes the old table.

Either way, any data in columns you removed is lost — everything else is preserved.

Note

SQLite Expert wraps the whole operation in a transaction. If anything goes wrong, it is rolled back automatically and the table is left exactly as it was, so you can correct the problem and try again.

Tip

You do not have to work out which route an edit will take: open the Change Script tab and look. A script full of ALTER TABLE statements is the direct route; one that creates a table with a _sqliteexpert_temp_table_ name is a restructure.

Note

If you would rather every structural change went the restructure route, as it did in version 5 and earlier, choose Always rebuild the table under Tools → Options → Code Generation → Compatibility.

When a change is refused because something else is broken

Renaming a table, renaming a column and removing a column all require SQLite to read every object that refers to the table, so that the change can be carried into them. If any object in the database no longer works, SQLite refuses — and reports it like this:

error in view v_sales: no such table: main.orders

The object at fault need not have anything to do with the table you are editing. A view built on a table someone deleted last year will stop you renaming a column in an unrelated table today.

SQLite Expert names the object and changes nothing. Correct or delete it, and the change will go through.

Not every change is affected: adding a column takes nothing into any other object, and neither does a change applied by restructuring. So one edit to a table can be refused while another succeeds.

Tip

It is worth fixing the object rather than looking for a way round the message. It means part of the database is already broken, and the change was about to be applied on top of it.

Previewing the change script

Before you apply anything, open the Change Script tab to see the exact SQL that SQLite Expert will run.

Tip

Reviewing the change script is a good habit — it shows precisely what will happen to your table, and is an easy way to learn how restructuring works.

The Last Change Script

The change script describes the difference between the database and the editor, so a successful apply empties it — the moment the work is done, the script that did it is gone.

The Last Change Script tab keeps it. After each apply it shows the SQL that ran on that object, so you can review what happened, or copy it to run against another database. If the apply renamed the object, the script is kept under its new name.

Each transaction in the record is followed by a comment saying how it ended — either -- This executed with no errors. or the error that stopped it. A failed apply is recorded too, which is when the record is most worth having; the table is left exactly as it was, and the script stays filed under the object's current name.

Note

This is remembered for the current session only. Closing SQLite Expert clears it.

If you have turned on Restore quoting style in DDL rewritten by SQLite, renaming a column adds a second step that cannot appear on the Change Script tab, because it depends on the schema as it stands after the rename. The change script explains where that step will happen, and the Last Change Script tab shows it in full afterwards.

See also: Editing a table · Changing options · Limitations