Problem adding values on table with FK

[Ajotah98]

Hello all,

I'm having a problem (I guess it's not related at all with SQLPage) but I'cant find solution, and as I'm building the app with, I want to ask for help...

I'm trying to insert data into a table on SQLite, called TICKETS:

create_ticket.sql

select * from shell;
INSERT INTO TICKETS (TICKETNUMBER, TICKETDESC,LABORCODE,DATEOFCREATION,INSTALLATION) VALUES (
    :TICKETNUMBER,
    :TICKETDESC,
    :LABORCODE,
    :DATEOFCREATION,
    :INSTALLATION
)
RETURNING
    'alert'   as component,
    'Success' as title,
    'Ticket created correctly.' as description,
    'check'   as icon,
    'green'   as color;

And this is the form:

ticket_form.sql

select * from shell;
select 
    'form' as component,
    'create_ticket.sql' as action,
    'Ticket creation' as title,
    'Create ticket' as validate,
    'Clear' as reset;
select 
    'Ticket number' as label,
    'TICKETNUMBER' as name,
    6 as width;
select
    'Labor code' as label,
    'LABORCODE' as name,
    6 as width,
    'select' as type,
    (SELECT
    json_group_array(
            json_object(
                'label',laborcodeid || ' - ' || laborcodename,
                'value',laborcodeid
            )
        )) AS options
    FROM (
        SELECT laborcodeid, laborcodename
        FROM laborcodes
        ORDER BY laborcodeid
    ) AS ordered_laborcodes;
select 
    'Ticket description' as label,
    'TICKETDESC' as name,
    'textarea' as type;
select
    'Installation' as label,
    'INSTALLATION' as name,
    'select' as type,
    (select 
        json_group_array(
            json_object( 
                    'label',installationid || ' - ' || installationname,
                    'value',installationid
                )
            )
        ) as options
        from 
        INSTALLATIONS;
select
    'Date of creation' as label,
    'DATEOFCREATION' as name,
    'date' as type;

As you can see, "INSTALLATION" on the form is a select-type data.

So far it works, but when I do the action to create_ticket.sql:

I'm having these response:

Failed to execute SQL statement:
INSERT INTO TICKETS (TICKETNUMBER, TICKETDESC, LABORCODE, DATEOFCREATION, INSTALLATION) VALUES (CAST(? AS TEXT), CAST(? AS TEXT), CAST(? AS TEXT), CAST(? AS TEXT), CAST(? AS TEXT)) RETURNING 'alert' AS component, 'Success' AS title, 'Ticket created correctly.' AS description, 'check' AS icon, 'green' AS color

error returned from database: (code: 1) foreign key mismatch - "TICKETS" referencing "INSTALLATIONS"
(code: 1) foreign key mismatch - "TICKETS" referencing "INSTALLATIONS"

And now, You can guess: He have a problem in his database structure, but...

This is the data in the DB:

And the inserts:

INSERT INTO INSTALLATIONS VALUES (1632,'A');
INSERT INTO INSTALLATIONS VALUES (1691,'B');
INSERT INTO INSTALLATIONS VALUES (1886,'C');
INSERT INTO INSTALLATIONS VALUES (2303,'D');
INSERT INTO INSTALLATIONS VALUES (2422,'E');
INSERT INTO INSTALLATIONS VALUES (2537,'F');
INSERT INTO INSTALLATIONS VALUES (2717,'G');
INSERT INTO INSTALLATIONS VALUES (2743,'H');

I'm not seeing the problem anywhere..

Edit:

My bad, I didn't add the DDL structure:

CREATE TABLE IF NOT EXISTS INSTALLATIONS  (
    INSTALLATIONID INTEGER NOT NULL,
    INSTALLATIONNAME TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS LABORCODES  (
    LABORCODEID INTEGER NOT NULL,
    LABORCODENAME TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS TICKETS  (
    TICKETID INTEGER PRIMARY KEY AUTOINCREMENT,
    TICKETNUMBER INTEGER NOT NULL, -- ok
    TICKETDESC TEXT NOT NULL, -- ok
    LABORCODE INTEGER NOT NULL, -- ok
    DATEOFCREATION DATE NOT NULL, --ok
    INSTALLATION INTEGER NOT NULL, --ok
    ISCLOSED BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (LABORCODE) REFERENCES LABORCODES(LABORCODEID),
    FOREIGN KEY (INSTALLATION) REFERENCES INSTALLATIONS(INSTALLATIONID)
);

[lovasoa]

Hello !

This is a very clear and well-detailed issue report, thank you.

The answer to your question is in this page of the sqlite documentation: https://www.sqlite.org/foreignkeys.html

. Foreign key DML errors are reported if:

• The parent table does not exist, or
• The parent key columns named in the foreign key constraint do not exist, or
• The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or
• The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.

The third point means that you cannot declare a foreign key constraint to INSTALLATIONID if it is not the primary key of INSTALLATIONS. It is quite counter-intuitive that the error is reported at insertion time instead of at the time when the foreign key is defined, but this is how sqlite works.

PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS INSTALLATIONS(INSTALLATIONID INTEGER NOT NULL); -- this allows multiple rows in the table to share the same INSTALLATIONID, probably not what you want anyway
CREATE TABLE IF NOT EXISTS TICKETS(INSTALLATION INTEGER NOT NULL, FOREIGN KEY (INSTALLATION) REFERENCES INSTALLATIONS(INSTALLATIONID));
INSERT INTO INSTALLATIONS VALUES (1632);
INSERT INTO TICKETS VALUES (1632);
    -- Error: in prepare, foreign key mismatch - "TICKETS" referencing "INSTALLATIONS" (1)

but

PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS INSTALLATIONS(INSTALLATIONID INTEGER PRIMARY KEY);
CREATE TABLE IF NOT EXISTS TICKETS(INSTALLATION INTEGER NOT NULL, FOREIGN KEY (INSTALLATION) REFERENCES INSTALLATIONS(INSTALLATIONID));
INSERT INTO INSTALLATIONS VALUES (1632);
INSERT INTO TICKETS VALUES (1632);
-- no error

TLDR The solution is to change your definition of the INSTALLATIONS table to make INSTALLATIONID the primary key.

[Ajotah98]

Damn... I just didn't realize that I can just put the primary key without the keyword autoincrement.

Thank you mate.