4

I am trying to reproduce the example from Introducing many-to-many (N-M) relations using Spatialite. Creating the tables and the relations works fine, but when I try to create a child element in a form in QGIS, the junction table cannot be saved because of a failed foreign key constraint. What am I doing wrong?

This is what I did:

  1. Create a new Spatialite database using spatialite_gui (SQLite version 3.22.0, SpatiaLite version 4.3.0a)

  2. Create three tables, airports, airlines, and a junction table airports_airlines using the following SQL statements:

    CREATE TABLE airports (
        id INTEGER PRIMARY KEY AUTOINCREMENT,   
        airport_name text NOT NULL
    );
    
    CREATE TABLE airlines (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        airline_name
        text NOT NULL
    );
    
    CREATE TABLE airports_airlines (
        id INTEGER PRIMARY KEY AUTOINCREMENT,   
        airport_fk INTEGER
            REFERENCES airports (id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
            DEFERRABLE INITIALLY DEFERRED,
        airline_fk INTEGER
            REFERENCES airlines (id)
            ON DELETE CASCADE
            ON UPDATE CASCADE
            DEFERRABLE INITIALLY DEFERRED
    );
    
  3. Load the three tables in QGIS (version 3.12.0).

  4. In Project Properties | Data Sources activate the option "Automatically create transaction groups where possible"

  5. In Project Properties | Relations, set the relations with the junction table as shown below:

  6. In the layer properties, set the cardinality of the relation to airports (id) and airlines (id), respectively, add the relations widget to the drag-and-drop form and remove the id text widget, as shown below:

  7. Try to create a new airline and an associated airport at the same time. This seems to work fine at first, but trying to save the layers fails with the following error messages

    CRITICAL    Layer airports_airlines : SQLite error: FOREIGN KEY constraint failed
              SQL: INSERT INTO "airports_airlines"( "airport_fk","airline_fk") VALUES ( ?,?)
    CRITICAL    Layer airports_airlines : SQLite error: FOREIGN KEY constraint failed
              SQL: INSERT INTO "airports_airlines"( "airport_fk","airline_fk") VALUES ( ?,?)
    

  • Do you have relation errors in the logs panel? – Gabriel De Luca Jun 13 '20 at 2:39
  • @GabrielDeLuca: Oh, yes, there are the following warnings: "WARNING Layer airports_airlines, field airport_fk: Missing Relation in configuration" and "WARNING Layer airports_airlines, field airline_fk: Missing Relation in configuration". Not sure how to interpret that, though, because the required relations are set up. – Jake Jun 13 '20 at 8:30
  • 1
    You need to use autoincrement for the id as at the moment, when you create any element, it does not have one set in the database. I thing that's why it can't work. Replace id INTEGER PRIMARY KEY, with id INTEGER PRIMARY KEY AUTOINCREMENT,. In the original post/tutorial, they use a type called serial present in PostgreSQL and not equivalent in SQLite stackoverflow.com/questions/19726028/… – ThomasG77 Jun 13 '20 at 10:44
  • @ThomasG77: Ah yes, thanks, that gets me a little bit further! However, saving the junction table now fails with a foreign key error (see my edited post). Could this be related to the fact that QGIS uses a transaction group for PostgreSQL and Geopackage layers, but apparently not for Spatialite layers? – Jake Jun 13 '20 at 11:39
  • See changelog.qgis.org/en/qgis/version/3.14/… as it may change the behaviour in QGIS 3.14 and maybe solve the problem?! – ThomasG77 Jun 21 '20 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.