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])
)
Carlos :
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.\n\nExample:\n\nCREATE TABLE IF NOT EXISTS parents\n(\n key1 INTEGER NOT NULL,\n key2 INTEGER NOT NULL,\n not_key INTEGER DEFAULT 0,\n\n PRIMARY KEY ( key1, key2 )\n);\n\n\nCREATE TABLE IF NOT EXISTS childs\n(\n child_key INTEGER NOT NULL,\n parentKey1 INTEGER NOT NULL,\n parentKey2 INTEGER NOT NULL,\n some_data INTEGER,\n\n PRIMARY KEY ( child_key ),\n FOREIGN KEY ( parentKey1, parentKey2 ) REFERENCES parents( key1, key2 )\n);\n",
2011-09-24T22:49:21
Vinay :
I am not sure about SQLite. But I found this link on google. http://www.sqlite.org/foreignkeys.html. \nSome of the reasons can be \n\n\nThe parent table does not exist, or\nThe parent key columns named in the foreign key constraint do not exist, or\nThe 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\nThe 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.\n",
2011-07-27T17:43:37