Limitations¶

Restructuring a table means rebuilding it under a temporary name (see Applying the changes), which foreign keys in other tables cannot follow. A few operations are therefore restricted when a table takes part in foreign-key relationships.

These restrictions apply only to changes that need the table restructured. A change SQLite can apply directly — renaming a column, adding or removing one, switching NOT NULL on or off, renaming the table — leaves every reference where it is and is not restricted. Renaming a column that another table's foreign key refers to is allowed for exactly that reason: SQLite carries the new name into the referring table's definition.

When the table is referenced by a foreign key from another table that holds rows, and a transaction is in progress, a change that needs the table restructured is refused. The changes that need it include:

changing a column's type, its DEFAULT or its collation, or moving it to another position;
deleting a column that belongs to the primary key or to a UNIQUE constraint — which is what a foreign key in another table refers to;
adding, deleting, or modifying a primary key, a UNIQUE constraint, or a foreign key;
pasting primary keys from the clipboard;
any change at all while Tools → Options → Code Generation → Compatibility is set to Always rebuild the table.

The Change Script tab tells the two paths apart: a script that mentions _sqliteexpert_temp_table_ is restructuring the table.

Tip

If a change is refused, commit or roll back the current transaction and apply it again — outside a transaction the rebuild switches foreign keys off around itself, which it cannot do inside one. If you must make the change without leaving the transaction, temporarily remove the foreign keys that reference the table, restructure it, then add them back.