SQLite 外键不匹配错误 [英] SQLite foreign key mismatch error

问题描述

为什么在执行下面的脚本时会出现 SQLite外键不匹配"错误?

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

这里是主表定义:

 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])
    )

和引用的表:

    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])
    )

推荐答案

当您在具有复合主键的表上使用外键时，您必须使用复合外键以及位于主键中的所有字段引用的表.

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.

示例:

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 )
);

这篇关于SQLite 外键不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！