SQLite foreign key mismatch error

Question

Why am I getting a SQLite "foreign key mismatch" error when executing script below?

DELETE 
FROM rlsconfig 
WHERE importer_config_id=2 and 
program_mode_config_id=1

Here is main table definition:

 CREATE TABLE [RLSConfig] (
        "rlsconfig_id"      integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        "importer_config_id"        integer NOT NULL,
        "program_mode_config_id"        integer NOT NULL,
        "l2_channel_config_id"      integer NOT NULL,
        "rls_fixed_width"       integer NOT NULL
    ,
        FOREIGN KEY ([importer_config_id])
            REFERENCES [ImporterConfig]([importer_config_id]),
        FOREIGN KEY ([program_mode_config_id])
            REFERENCES [ImporterConfig]([importer_config_id]),
        FOREIGN KEY ([importer_config_id])
            REFERENCES [ImporterConfig]([program_mode_config_id]),
        FOREIGN KEY ([program_mode_config_id])
            REFERENCES [ImporterConfig]([program_mode_config_id])
    )

and referenced table:

    CREATE TABLE [ImporterConfig] (
        "importer_config_id"        integer NOT NULL,
        "program_mode_config_id"        integer NOT NULL,
        "selected"      integer NOT NULL DEFAULT 0,
        "combined_config_id"        integer NOT NULL,
        "description"       varchar(50) NOT NULL COLLATE NOCASE,
        "date_created"      datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),
        PRIMARY KEY ([program_mode_config_id], [importer_config_id])
    ,
        FOREIGN KEY ([program_mode_config_id])
            REFERENCES [ProgramModeConfig]([program_mode_config_id])
    )

Answer 1

When you use a foreign key over a table that has a composite primary key you must use a composite foreign key with all the fields that are in the primary key of the referenced table.

Example:

CREATE TABLE IF NOT EXISTS parents
(
    key1 INTEGER NOT NULL,
    key2 INTEGER NOT NULL,
    not_key INTEGER DEFAULT 0,

    PRIMARY KEY ( key1, key2 )
);


CREATE TABLE IF NOT EXISTS childs
(
    child_key INTEGER NOT NULL,
    parentKey1 INTEGER NOT NULL,
    parentKey2 INTEGER NOT NULL,
    some_data INTEGER,

    PRIMARY KEY ( child_key ),
    FOREIGN KEY ( parentKey1, parentKey2 ) REFERENCES parents( key1, key2 )
);

Answer 2

I am not sure about SQLite. But I found this link on google. http://www.sqlite.org/foreignkeys.html. Some of the reasons can be

• 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.

Answer 3

Unfortunately, SQLite gives this error all the time without mentioning WHICH foreign key constraint failed. You are left to try to check them one by one, which often doesn't work, and then rebuild the table without the constraints and add them back one by one until you find the problem. SQLite is great in a lot of ways, but this isn't one of them.