Details for the issue

What did you do?

I added Not Null & Unique to the primary keys of some tables with the "Database structure" "Modify table" GUI of DB4S.

What did you expect to see?

Just the attributes changed

What did you see instead?

Entire dependent table emptied. (!)

Here is what I think happened:

The dependent table has a number of fields that use FOREIGN KEY clauses to reference the tables where I added "Not Null" and "Unique". The "references" clauses also include on update and on delete cascade.

The (clever DB4S) implementation of checking "NN" and "U" is that the tables are copied to a temporary table; the original is DROPped and the temporary table is renamed to replace the original. (This does take a while with a several million row table...)

The DROP caused an implicit DELETE of all rows, triggering the dependent table's "on delete" clause per, emptying it.

If foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations.

This all makes sense at the statement level - but is unexpected in the GUI.

You appear to be trying to handle this by bracketing the sequence with
PRAGMA defer_foreign_keys = "1";

PRAGMA defer_foreign_keys = ;

However, this doesn't prevent the implicit DELETEs. I suspect that the (untested) easy fix is to use
PRAGMA foreign_keys = 0;
instead. This is suggested in the referenced page on sqlite.org:

The properties of the DROP TABLE and ALTER TABLE commands described above only apply if foreign keys are enabled. If the user considers them undesirable, then the workaround is to use PRAGMA foreign_keys to disable foreign key constraints before executing the DROP or ALTER TABLE command. Of course, while foreign key constraints are disabled, there is nothing to stop the user from violating foreign key constraints and thus creating an internally inconsistent database.

Since you are replicating the existing table's contents, the result should not cause any (new) foreign key constraint violations.

Here is an abbreviated copy of the SQL log in one such sequence:

PRAGMA foreign_keys = "1";
PRAGMA database_list;
SELECT type,name,sql,tbl_name FROM "main".sqlite_master;
PRAGMA encoding
PRAGMA foreign_keys
CREATE TABLE "sqlb_temp_table_1" (
	"id"	integer UNIQUE,
[snip]
	FOREIGN KEY("cdid") REFERENCES "CDs"("diskid"),
	PRIMARY KEY("id")
);
INSERT INTO "main"."sqlb_temp_table_1" SELECT "id",[snip] FROM "main"."FILEs";
PRAGMA defer_foreign_keys
PRAGMA defer_foreign_keys = "1";
DROP TABLE "main"."FILEs";
ALTER TABLE "main"."sqlb_temp_table_1" RENAME TO "FILEs"
PRAGMA defer_foreign_keys = "0";
CREATE UNIQUE INDEX "pathindex" ON "FILEs" (
[snip]

Useful extra information

The info below often helps, please fill it out if you're able to. :)

What operating system are you using?

• [x ] Windows: ( _version:_10 pro ___ )
• Linux: ( distro: ___ )
• Mac OS: ( version: ___ )
• Other: ___

What is your DB4S version?

• 3.10.1
• 3.10.0
• 3.9.1
• Other: _7-Jul nightly

Did you also

• Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20