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’m trying to create an Associative Entity (N:N) in SQLite like this:
[ Pet —< VaccinePet >— Vaccine ]
And, I have the follow code of my associative entity:
CREATE TABLE VACINAPET (
vp_date TEXT NOT NULL,
vp_is_applied INTEGER DEFAULT 0,
fk_pet INTEGER,
fk_vaccine INTEGER,
FOREIGN KEY (fk_pet) REFERENCES pet (pet_id),
FOREIGN KEY (fk_vaccine) REFERENCES vaccine (vaccine_id),
PRIMARY KEY (fk_pet, fk_vaccine)
);
BUT, I’m getting an error:
foreign key mismatch — «VACCINEPET» referencing «vaccine»: INSERT INTO
VACCINEPET (vp_date, vp_is_applied, fk_pet, fk_vaccine) VALUES
(’23/05/2018′, 0, 1, 1);
When I try to use the INSERT command:
INSERT INTO VACINAPET (vp_data, vp_is_aplicada, fk_pet, fk_vacina)
VALUES (’23/05/2018′, 0, 1, 1);
What could be wrong? I’m not so good in database… 
MASTER DETAIL: I have data in Pet table and Vaccine table, they are not empty
Обновлено
Вопрос:
Я уже проверил этот вопрос и подумал, что у меня есть ответ, но потом это выглядело не так.
У меня есть следующий пример:
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)
Having worked with databases for over 30 years, and after looking over your database definitions, I see several issues with your database that should be corrected, restructured, or optimized. I am not going to go into all of those issues since this is primarily an AutoIt forum not a SQL and/or database forum. However, I will try to answer your specific question as to why you are getting this particular foreign key error, how you can begin to trouble shoot such issues in SQLite in the future, and one way that you can resolve this particular error.
First, to identify glaring foreign key issues, you can execute the following SQLite pragma command on the database. When you execute the command on the DB that you attached, you will see the following error. The pragma command will not find all foreign key errors, just the ones that the processor can detect at the time.
pragma foreign_key_check; Result: foreign key mismatch - "postinstall_user" referencing "postinstall"
Why are you getting that error? The following excerpt from the SQLite documentation will shed some light on the issue:
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. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table. . . . If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist. 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.
In this particular case, the third bullet, above, is the reason why you are having an issue. If you refer back to the result of the pragma command, it say that there is a foreign key defined in the postinstall_user table that references the postinstall table, that has a problem. Your postinstall_user table is defined as:
CREATE TABLE [postinstall_user] ( [installer_id] INTEGER NOT NULL REFERENCES [installer]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, [sequence_id] INTEGER NOT NULL REFERENCES [postinstall]([sequence_id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, [user_id] INTEGER NOT NULL REFERENCES [user]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY ( [installer_id], [sequence_id], [user_id] ) )
As you can see, there is one foreign key in that table that references the postinstall table. sequence_id is not the complete primary key in the postinstall table. The primary key in that table is defined as «PRIMARY KEY([installer_id], [sequence_id]))». Therefore to satisfy the third bullet of the documentation, if you change the definition of that foreign key to match the primary key in the postinstall table, it should fix your issue. So the definition of that postinstall_user table should look something like:
CREATE TABLE postinstall_user ( installer_id INTEGER NOT NULL, sequence_id INTEGER NOT NULL, user_id INTEGER NOT NULL, PRIMARY KEY (installer_id, sequence_id, user_id), FOREIGN KEY (installer_id, sequence_id) REFERENCES postinstall (installer_id, sequence_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED );
After making that change, when I delete the installer record or a user record, the related records, as you defined them, are successfully deleted.
I hope that helps.
Installer — New.db
Edited by TheXman
Attached modified DB
Hi,
I’ve spent most of a day trying to figure out what was causing a
«foreign key mismatch» error in a DELETE query. I’m using
version…
How It Works
Get an answer in three easy steps. Here’s how it works…
1. Ask Your Question
Enter your SQLite question at the top of this page and click Get An Answer.
2. Pick Your Priority
Tell us how quickly you want your SQLite question answered.
3. Get An Answer
Connect with your programmer via online chat or telephone call.
Answer
Customer
Hi,
I’ve spent most of a day trying to figure out what was causing a
«foreign key mismatch» error in a DELETE query. I’m using
version 3.7.4.
First the details. There are three tables: NODES_RUNTIME,
WORKFLOW_RUNTIME and SCHEDULER. Table NODES_RUNTIME has a foreign
key reference to the primary key of parent table WORKFLOW_RUNTIME and
foreign key reference to the primary key of another parent table
SCHEDULER. All foreign key references corresponding to
WORKFLOW_RUNTIME are set to ON DELETE CASCADE.
Now when I delete data from WORKFLOW_RUNTIME, I expect that delete to
cascade into NODES_RUNTIME, but it was causing a «foreign key
mismatch» error.
I am also setting: PRAGMA foreign_keys=ON;
My table structure is :
NODES_RUNTIME (
NODE_RUN_ID INTEGER NOT NULL,
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
SCHEDULED_ID INTEGER,
FOREIGN KEY(PART_NO) REFERENCES WORKFLOW_RUNTIME(PART_NO) ON
DELETE CASCADE,
FOREIGN KEY(WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(WF_RUN_ID) ON
DELETE CASCADE,
FOREIGN KEY(SCHEDULED_ID) REFERENCES SCHEDULER(SCH_ID),
PRIMARY KEY(NODE_RUN_ID) );
WORKFLOW_RUNTIME (
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
PRIMARY KEY(WF_RUN_ID,PART_NO) );
SCHEDULER (
SCH_ID INTEGER NOT NULL,
PRIMARY KEY (SCH_ID));
Query is : delete from WORKFLOW_RUNTIME where part_no = 1 and
WF_RUN_ID = ‘xyz’;
Error: foreign key mismatch
Please let me know that what is issue here and how can i solve this
issue
Thanks
Abhishek
srl309
Programmer
If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are «DML errors» and errors reported when the schema is changed are «DDL errors». So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually «foreign key mismatch» but can also be «no such table» if the parent table does not exist. Foreign key DML errors are may be 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.
FROM http://www.sqlite.org/foreignkeys.html
I think your foreign keys are incorrect
maybe this FOREIGN KEY(PART_NO) REFERENCES WORKFLOW_RUNTIME(PART_NO) ON DELETE CASCADE,
FOREIGN KEY(WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(WF_RUN_ID) ON DELETE CASCADE,
should be like:
FOREIGN KEY(PARTNO, WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(PARTNO, WF_RUN_ID) ON DELETE CASCADE
srl309
Programmer
If i was right and if you want the foreign keys you had on Nodes_run time you probably need another table:
PARTS
PARTNO PK
name
WORKFLOW_RUNTIME
WFRUNID PK
PARTNO FK REFERENCES PARTS(PART_NO)
instead of the WORKFLOW_RUNTIME table
Customer
[quote=’srl309′ pid=’128′ dateline=’ ***-***-**** ‘]
If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are «DML errors» and errors reported when the schema is changed are «DDL errors». So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually «foreign key mismatch» but can also be «no such table» if the parent table does not exist. Foreign key DML errors are may be 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.
FROM http://www.sqlite.org/foreignkeys.html
I think your foreign keys are incorrect
maybe this FOREIGN KEY(PART_NO) REFERENCES WORKFLOW_RUNTIME(PART_NO) ON DELETE CASCADE,
FOREIGN KEY(WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(WF_RUN_ID) ON DELETE CASCADE,
should be like:
FOREIGN KEY(PARTNO, WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(PARTNO, WF_RUN_ID) ON DELETE CASCADE
[/quote]
Dear Srl309,
Thanks for your reply.
As you told i gave my foreign like
FOREIGN KEY(PART_NO, WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(PARTNO, WF_RUN_ID) ON DELETE CASCADE
but still i am getting same error.
Now my table structure is:
CREATE TABLE NODES_RUNTIME (
NODE_RUN_ID INTEGER NOT NULL,
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
SCHEDULED_ID INTEGER,
FOREIGN KEY(PART_NO, WF_RUN_ID) REFERENCES WORKFLOW_RUNTIME(PARTNO, WF_RUN_ID) ON DELETE CASCADE
FOREIGN KEY(SCHEDULED_ID) REFERENCES SCHEDULER(SCH_ID),
PRIMARY KEY(NODE_RUN_ID) );
CREATE TABLE SCHEDULER (
SCH_ID INTEGER NOT NULL,
PRIMARY KEY (SCH_ID));
CREATE TABLE WORKFLOW_RUNTIME (
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
PRIMARY KEY(WF_RUN_ID,PART_NO) );
I want to know one more think is that when should i put PRAGMA foreign_keys=ON, before that table creation or before firing the query or i can put any time?
Please correct me for
Error: foreign key mismatch
and PRAGMA issue.
Thanks,
Abhishek[hr]
[quote=’srl309′ pid=’129′ dateline=’ ***-***-**** ‘]
If i was right and if you want the foreign keys you had on Nodes_run time you probably need another table:
PARTS
PARTNO PK
name
WORKFLOW_RUNTIME
WFRUNID PK
PARTNO FK REFERENCES PARTS(PART_NO)
instead of the WORKFLOW_RUNTIME table
[/quote]
Dear Srl309,
My problem is that i can not make PARTS as a table.
Please suggest me solution for
Error: foreign key mismatch
Thanks,
Abhishek
srl309
Programmer
CREATE TABLE NODES_RUNTIME (
NODE_RUN_ID INTEGER PRIMARY KEY NOT NULL,
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
SCH_ID INTEGER NOT NULL,
FOREIGN KEY(WF_RUN_ID, PART_NO) REFERENCES WORKFLOW_RUNTIME(WF_RUN_ID, PART_NO) ON DELETE CASCADE,
FOREIGN KEY (SCH_ID) REFERENCES SCHEDULER(SCH_ID)
);
CREATE TABLE SCHEDULER(
SCH_ID INTEGER PRIMARY KEY NOT NULL
);
CREATE TABLE WORKFLOW_RUNTIME (
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
PRIMARY KEY(WF_RUN_ID, PART_NO) );
Try the schema above
it seemed to work for me
sqlite> .read a.sql
sqlite> INSERT INTO SCHEDULER VALUES(1);
sqlite> pragma foreign_keys=on;
sqlite> INSERT INTO workflow_runtime values (‘a’, 1, ‘a’);
sqlite> INSERT INTO nodes_runtime values (1, ‘a’, 1, ‘a’, 1);
sqlite> delete from WORKFLOW_RUNTIME where part_no = 1 and
…> WF_RUN_ID = ‘a’;
sqlite> select * from workflow_runtime;
sqlite> select * from nodes_runtime;
sqlite> select * from scheduler;
1
If you put the pragma before table creation checks if there is that value is in the other table with primary key. Therefore pragma before will catch incorrect insertions into the tables.
example
sqlite> INSERT INTO nodes_runtime values (2,’c’,1,’c’,1);
Error: foreign key constraint failed
Customer
[quote=’srl309′ pid=’131′ dateline=’ ***-***-**** ‘]
CREATE TABLE NODES_RUNTIME (
NODE_RUN_ID INTEGER PRIMARY KEY NOT NULL,
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
SCH_ID INTEGER NOT NULL,
FOREIGN KEY(WF_RUN_ID, PART_NO) REFERENCES WORKFLOW_RUNTIME(WF_RUN_ID, PART_NO) ON DELETE CASCADE,
FOREIGN KEY (SCH_ID) REFERENCES SCHEDULER(SCH_ID)
);
CREATE TABLE SCHEDULER(
SCH_ID INTEGER PRIMARY KEY NOT NULL
);
CREATE TABLE WORKFLOW_RUNTIME (
WF_RUN_ID varchar(36) NOT NULL,
PART_NO INTEGER NOT NULL,
NAME varchar(45) NOT NULL,
PRIMARY KEY(WF_RUN_ID, PART_NO) );
Try the schema above
it seemed to work for me
sqlite> .read a.sql
sqlite> INSERT INTO SCHEDULER VALUES(1);
sqlite> pragma foreign_keys=on;
sqlite> INSERT INTO workflow_runtime values (‘a’, 1, ‘a’);
sqlite> INSERT INTO nodes_runtime values (1, ‘a’, 1, ‘a’, 1);
sqlite> delete from WORKFLOW_RUNTIME where part_no = 1 and
…> WF_RUN_ID = ‘a’;
sqlite> select * from workflow_runtime;
sqlite> select * from nodes_runtime;
sqlite> select * from scheduler;
1
If you put the pragma before table creation checks if there is that value is in the other table with primary key. Therefore pragma before will catch incorrect insertions into the tables.
example
sqlite> INSERT INTO nodes_runtime values (2,’c’,1,’c’,1);
Error: foreign key constraint failed
[/quote]
Dear Srl309,
Thanks for your support.
Now it is working for me.
Thanks,
Abhishek
Featured Programmer
Byron Narciso
Programmer
Byron has over 15 years of Technical Support Experience with expertise on Windows, Apple devices , Android, Virus Removal, Cloud Computing, Email Configuration, Smartphones, Facebook, Instagram, Kindle, Twitter,Dropbox, Linkedin, Printer configurations and Networking. He has worked for some of the top computer manufacturers in the world and has served as a Subject Matter Expert for Level 1 and 2 technicians providing assistance in resolving advance technical issues. Byron holds a degree in Electronics and Communications Engineering with a DSCE and CCNA certification.
About ExpertHelp
ExpertHelp is changing the way you connect with service professionals.
Whether you have a quick question while preparing your taxes, troubleshooting a computer problem, or need to hire an attorney, ExpertHelp is the most convenient and affordable way to connect with the right service professional to get the job done.
ExpertHelp has been in business since 2011, is an A+ Rated Better Business Bureau accredited member, and offers a 100% satisfaction guarantee on every question you ask!