Ошибка при подтверждении новой строки foreign key mismatch

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

I already checked out this question, and thought I had the answer — but then it didn’t look right to me.

I have the following pared down example:

CREATE TABLE pipelines (                                                        
        name VARCHAR NOT NULL,                                                  
        owner VARCHAR NOT NULL,                                                 
        description VARCHAR,                                                    
        PRIMARY KEY (name, owner),                                              
        FOREIGN KEY(owner) REFERENCES user (id)                                 
);                                                                              
CREATE TABLE tasks (                                                            
        id INTEGER NOT NULL,                                                    
        title VARCHAR,                                                          
        pipeline VARCHAR,                                                       
        owner VARCHAR,                                                          
        PRIMARY KEY (id),                                                       
        FOREIGN KEY(pipeline) REFERENCES pipelines (name),                      
        FOREIGN KEY(owner) REFERENCES pipelines (owner)                         
);                                                                              
CREATE TABLE user (                                                           
        id VARCHAR NOT NULL,                                                    
        name VARCHAR,                                                           
        password VARCHAR,                                                       
        PRIMARY KEY (id)                                                        
);                                                                              
pragma foreign_keys=on;                                                         

insert into user values ('wayne', '', '');                                    
insert into pipelines values ('pipey', 'wayne', '');                            
insert into tasks values (1, 'hello', 'pipey', 'wayne'); 

When executing this code, it bails out:

$ sqlite3 foo.sq3 '.read mismatch.sql'    
Error: near line 27: foreign key mismatch

Through the list in the question I cited:

• the parent table (user) exists.
• the parent columns (name, owner) exist
• the parent columns are, in fact, the primary key (I thought that may have been it originally)
• the child table references all of the primary key columns in the parent table

So what in the world could be causing this error?

I already checked out this question, and thought I had the answer — but then it didn’t look right to me.

I have the following pared down example:

CREATE TABLE pipelines (                                                        
        name VARCHAR NOT NULL,                                                  
        owner VARCHAR NOT NULL,                                                 
        description VARCHAR,                                                    
        PRIMARY KEY (name, owner),                                              
        FOREIGN KEY(owner) REFERENCES user (id)                                 
);                                                                              
CREATE TABLE tasks (                                                            
        id INTEGER NOT NULL,                                                    
        title VARCHAR,                                                          
        pipeline VARCHAR,                                                       
        owner VARCHAR,                                                          
        PRIMARY KEY (id),                                                       
        FOREIGN KEY(pipeline) REFERENCES pipelines (name),                      
        FOREIGN KEY(owner) REFERENCES pipelines (owner)                         
);                                                                              
CREATE TABLE user (                                                           
        id VARCHAR NOT NULL,                                                    
        name VARCHAR,                                                           
        password VARCHAR,                                                       
        PRIMARY KEY (id)                                                        
);                                                                              
pragma foreign_keys=on;                                                         

insert into user values ('wayne', '', '');                                    
insert into pipelines values ('pipey', 'wayne', '');                            
insert into tasks values (1, 'hello', 'pipey', 'wayne'); 

When executing this code, it bails out:

$ sqlite3 foo.sq3 '.read mismatch.sql'    
Error: near line 27: foreign key mismatch

Through the list in the question I cited:

• the parent table (user) exists.
• the parent columns (name, owner) exist
• the parent columns are, in fact, the primary key (I thought that may have been it originally)
• the child table references all of the primary key columns in the parent table

So what in the world could be causing this error?

В консоли пишет sqlite3.OperationalError: foreign key mismatch — «users» referencing «gang», без использования g_name все нормально, не использовать g_id — та же ошибка.

import sqlite3

with sqlite3.connect("data.db") as db:
	qwe = db.cursor()

qwe.execute('''PRAGMA foreign_keys=ON''')

qwe.execute(""" CREATE TABLE IF NOT EXISTS gang
(
    id INTEGER,
    name TEXT NOT NULL,
    PRIMARY KEY (id, name)
);""")

qwe.execute(""" CREATE TABLE IF NOT EXISTS users
(
    id INTEGER,
    name TEXT NOT NULL,
    g_id INTEGER,
    g_name TEXT,
    PRIMARY KEY (id, g_name)
    FOREIGN KEY (g_id) REFERENCES gang (id) ON DELETE CASCADE
    FOREIGN KEY (g_name) REFERENCES gang (name) ON UPDATE CASCADE
);""")

gid = input("gid:  ")
gname = input("gname:  ")

qwe.execute(''' INSERT INTO gang (id, name) VALUES (?, ?) ''', [gid, gname])

db.commit()

uid = input("uid:  ")
uname = input("uname:  ")
ugid = input("ugid:  ")
qwe.execute(''' INSERT INTO users (id, name, g_id,  g_name) VALUES (?, ?, ?, ?) ''', [uid, uname, ugid, gname])

db.commit()

Doh, sorry. The transaction workaround was to show the messages, but IIRC the foreign_keys pragma doesn’t have an effect inside a transaction.

That said, the same thing happens with raw sql:

-- partial_fk.sql

CREATE TABLE units (
    id TEXT NOT NULL,
    user TEXT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY (id, user)
);

CREATE TABLE modules (
    id TEXT NOT NULL,
    unit TEXT NOT NULL REFERENCES units (id),
    name TEXT NOT NULL,
    PRIMARY KEY (id, unit)
);

PRAGMA foreign_keys = ON;

INSERT INTO units (id, user, name) VALUES ('id', 'user', 'name');
INSERT INTO modules (id, unit, name) VALUES ('m_id', 'id', 'm_name');

If I run sqlite3 < partial_fk.sql I also get Error: near line 18: foreign key mismatch - "modules" referencing "units". It works as expected if I change the primary key of units to only include PRIMARY KEY (id).

I’ve re-read the docs on foreign keys in sqlite, and it says that

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index

I think that this is what might be failing here? units (id) is not the primary key, and doesn’t span a uniqueness constraint on its own.

I use C# with System.Data.SQLite library and I got a foreign key mismatch error. I created three tables:

Com.CommandText = @"create table Projekt ([ProjectID] Integer NOT NULL Primary key, [Projektname] varchar(360),[ProjektEd] DATETIME, [ProjektLSD] Datetime, [ProjektLD] Datetime);";
Com.ExecuteNonQuery();
Com.CommandText= "create table Template ([TemplateID] Integer NOT NULL Primary key,[Templatename] varchar(360),[TemplateEd] Datetime, [TemplateLSD] Datetime,[TemplateLD] Datetime);";
Com.ExecuteNonQuery();
Com.CommandText = "create table Messung ([MessungID] Integer Not Null Primary Key, [Messungname] varchar(360), [MessungEd] Datetime, [MessungLSD] Datetime, [MessungLD] Datetime, [ProjektID] Integer, [TemplateID] Integer,FOREIGN KEY(ProjektID) REFERENCES Projekt(ProjektID), Foreign Key (TemplateID) References Template(TemplateID)); ";
Com.ExecuteNonQuery();

After that I insert this creates my first entry in the «Projekt» table:

Insert into projekt (Projektname, ProjektEd,ProjektLSD, ProjektLD) values ('Messung_Win_3', '2016-06-16 14:47:00','2016-06-27 14:47:00', '2016-07-14 11:12:00');

While this creates my fourth entry (I have another three inserts before this one) in the table «Template»:

Insert into Template (Templatename, TemplateEd, TemplateLSD, TemplateLD) values('Messung_Win_3', '2016-05-19 15:13:00','2016-06-27 14:47:00', '2016-07-14 11:12:00');

So now to the question: Why does this command have a foreign key mismatch error?

Com.CommandText = "Insert into Messung (Messungname, MessungEd, ProjektID, TemplateID) values ('Board2ROI3','2016-06-16 14:47:00',1,4);";

I testet it via SQLFiddle with the SQLite (SQL.js). Link is here: http://sqlfiddle.com/

There it worked perfectly fine but in C# it gets this error.

I hope you can help me

c# sqlite system.data.sqlite

What I have tried:

Created it inside SQLFiddle where it works perfectly fine

CREATE TABLE "student_table" (
    "student_id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name"  TEXT NOT NULL,
    "gender"    TEXT NOT NULL,
    "year_admited"  INTEGER
);

CREATE TABLE "year_table" (
    "year"  INTEGER NOT NULL,
    "student_id"    INTEGER NOT NULL,
    "class" INTEGER NOT NULL,
    PRIMARY KEY("year"),
    FOREIGN KEY("student_id") REFERENCES "student_table"("student_id")
);

CREATE TABLE "terms_table" (
    "year"  INTEGER NOT NULL,
    "student_id"    INTEGER NOT NULL,
    "term"  INTEGER NOT NULL,
    "fees"  INTEGER,
    PRIMARY KEY("term","year"),
    FOREIGN KEY("year") REFERENCES "year_table"("year"),
    FOREIGN KEY("student_id") REFERENCES "year_table"("student_id")
);

INSERT INTO "main"."terms_table"
("year", "student_id", "term", "fees")
VALUES (2020, 1, 1, 900000);

Error adding record. Message from database engine:

foreign key mismatch - "terms_table" referencing "year_table" (INSERT INTO 
"main"."terms_table"
("year", "student_id", "term", "fees")
VALUES (2020, 1, 1, 900000);)

1 ответ

Лучший ответ

FOREIGN KEY("student_id") REFERENCES "year_table"("student_id")

FOREIGN KEY("student_id") REFERENCES "student_table"("student_id")

Я уже проверил этот вопрос и подумал, что у меня есть ответ, но потом это выглядело не так.

У меня есть следующий пример:

CREATE TABLE pipelines (
name VARCHAR NOT NULL,
owner VARCHAR NOT NULL,
description VARCHAR,
PRIMARY KEY (name, owner),
FOREIGN KEY(owner) REFERENCES user (id)
);
CREATE TABLE tasks (
id INTEGER NOT NULL,
title VARCHAR,
pipeline VARCHAR,
owner VARCHAR,
PRIMARY KEY (id),
FOREIGN KEY(pipeline) REFERENCES pipelines (name),
FOREIGN KEY(owner) REFERENCES pipelines (owner)
);
CREATE TABLE user (
id VARCHAR NOT NULL,
name VARCHAR,
password VARCHAR,
PRIMARY KEY (id)
);
pragma foreign_keys=on;

insert into user values ('wayne', '', '');
insert into pipelines values ('pipey', 'wayne', '');
insert into tasks values (1, 'hello', 'pipey', 'wayne');

При выполнении этого кода он выгружается:

$ sqlite3 foo.sq3 '.read mismatch.sql'
Error: near line 27: foreign key mismatch

Через список в вопросе, который я привел:

• существует родительская таблица (пользователь).
• существуют родительские столбцы (имя, владелец)
• родительские столбцы – это, по сути, первичный ключ (я думал, что это было изначально)
• дочерняя таблица ссылается на все столбцы первичного ключа в родительской таблице

И что в мире может вызвать эту ошибку?

В документации говорится:

Обычно родительский ключ ограничения внешнего ключа является первичным ключом родительской таблицы. Если они не являются первичным ключом, то столбцы родительского ключа должны быть совместно подчинены ограничению UNIQUE или иметь индекс UNIQUE.

В таблице pipelines ни столбцы name, ни столбцы owner сами по себе не уникальны.

Я думаю, вы действительно хотите иметь двухстоечный внешний ключ в таблице tasks:

FOREIGN KEY(pipeline, owner) REFERENCES pipelines(name, owner)