View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0007724VALENTINA STUDIOPLUGIN - DB - SQLite Localpublic2016-07-17 11:332019-09-27 21:37
ReporterRalf Bertling Assigned ToIgor Nikitin  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
PlatformALLOSALLOS VersionALL
Product Version6.4.x 
Fixed in Version9.6.x 
Summary0007724: Generated Schema changes remove links / foreign Keys on SQLite
DescriptionWhen using the GUI features to alter SQLite tables, existing foreign key constraints are silently dropped.

Due to limitations of SQLIte, a fresh Table is always created for ALTER TABLE
(Note: This would not be strictly necessary for Add Column, but that does not matter for my example)

The create Script for the intermediate Table does not contain foreign keys that were created earlier.
Steps To ReproduceCreate a new SQLite DB
Check the foreign key checkbox in DB-Properties.

Issue the following commands (or use the attached SQLite file.

BEGIN;

CREATE TABLE "a"(
    "A_Id" Integer NOT NULL PRIMARY KEY,
    "A_Name" Text NOT NULL COLLATE NOCASE );

CREATE TABLE "b"(
    "B_Id" Integer NOT NULL PRIMARY KEY,
    "B_Name" Text NOT NULL COLLATE NOCASE );

CREATE TABLE "c"(
    "a_id" Integer NOT NULL,
    "b_id" Integer NOT NULL,
    "c_id" Integer NOT NULL PRIMARY KEY,
    CONSTRAINT "lnk_c_a" FOREIGN KEY ( "a_id" ) REFERENCES "a"( "A_Id" )
 );

END;

Now, try to add another link from table c to b on the column b_Id: The following SQL is generated by Valentina, effectively dropping the link lnk_c_a:

BEGIN;

-- CREATE LINK "lnk_c_b" ---------------------------------------
DROP TABLE IF EXISTS "__vs_temp_table";

CREATE TABLE "__vs_temp_table"(
    "a_id" Integer NOT NULL,
    "b_id" Integer NOT NULL,
    "c_id" Integer NOT NULL PRIMARY KEY,
    CONSTRAINT "lnk_c_b" FOREIGN KEY ( "b_id" ) REFERENCES "b"( "B_Id" )
 );

INSERT INTO "__vs_temp_table"("a_id","b_id","c_id")
    SELECT "a_id","b_id","c_id" FROM "c";

DROP TABLE IF EXISTS "c";

ALTER TABLE "__vs_temp_table" RENAME TO "c";
-- -------------------------------------------------------------

COMMIT;
Additional InformationThe bug also seems to affect any other change on a table that links to another table.
TagsNo tags attached.
Working with DB:SQLite Local DB
Version of your DB:

Activities

Ralf Bertling

Ralf Bertling

2016-07-17 11:33

reporter  

bug.sqlite (7,168 bytes)

Issue History

Date Modified Username Field Change
2016-07-17 11:33 Ralf Bertling New Issue
2016-07-17 11:33 Ralf Bertling File Added: bug.sqlite
2019-09-27 21:37 Igor Nikitin Assigned To => Igor Nikitin
2019-09-27 21:37 Igor Nikitin Status new => resolved
2019-09-27 21:37 Igor Nikitin Resolution open => fixed
2019-09-27 21:37 Igor Nikitin Fixed in Version => 9.6.x