SQLite Error foreign key mismatch - Stock referencing Variants

Question

When I try delete all from table Products i get the error message in title SQLite Error 1:

foreign key mismatch - "Stock" referencing "Variants"

This is my tables definitions

CREATE TABLE Products ( Codart TEXT NOT NULL, Descri TEXT NOT NULL,...
CONSTRAINT PK_Products PRIMARY KEY(Codart) )

CREATE TABLE Variants ( Codart TEXT NOT NULL, Codvar TEXT NOT NULL, 
CONSTRAINT PK_Variants PRIMARY KEY(Codart, Codvar), 
CONSTRAINT FK_Products FOREIGN KEY (Codart) REFERENCES Products(Codart) ON DELETE CASCADE )

CREATE TABLE Stock (Year int not null, Codart text NOT NULL, Codvar text NOT NULL, Qty real NOT NULL, 
CONSTRAINT PK_Stock PRIMARY KEY(`Year`,`Codart`,`Codvar`), 
CONSTRAINT FK_Products FOREIGN KEY (Codart) REFERENCES Products(Codart) ON DELETE CASCADE, 
CONSTRAINT FK_Variants FOREIGN KEY (Codvar) REFERENCES Variants(Codvar) ON DELETE CASCADE )

Question is when i try to run "DELETE FROM Products" i get error above

Possible duplicate of [What is causing this sqlite foreign key mismatch?](https://stackoverflow.com/questions/18915331/what-is-causing-this-sqlite-foreign-key-mismatch) — JimmyB, Apr 01 '19 at 10:06
In short, `Stock` has a foreign key constraint to `Variants`, but not to its primary key or another `unique` set of fields. — JimmyB, Apr 01 '19 at 10:08

score 0 · Accepted Answer · answered Apr 01 '19 at 10:10

0

On Stock, try

CONSTRAINT FK_Variants FOREIGN KEY (Codart,Codvar) REFERENCES Variants(Codart,Codvar) ON DELETE CASCADE

answered Apr 01 '19 at 10:10

JimmyB

12,101
2
28
44

SQLite Error foreign key mismatch - Stock referencing Variants

1 Answers1