'Why am I getting a SQLite "foreign key mismatch" error when executing script below?

CREATE TABLE Zuliefererkooperiertmitzulieferer(Unternehmensnamen1 VARCHAR(80) NOT NULL CHECK(Unternehmensnamen1 NOT GLOB "*[^A-Za-z]*" AND length(Unternehmensnamen1)>0),
                                               Unternehmensnamen2 VARCHAR(80) NOT NULL CHECK(Unternehmensnamen2 NOT GLOB "*[^A-Za-z]*" AND length(Unternehmensnamen2)>0
                                               AND (Unternehmensnamen1 NOT LIKE Unternehmensnamen2)),
                                               PRIMARY KEY (Unternehmensnamen1,Unternehmensnamen2),
                                               FOREIGN KEY (Unternehmensnamen1) REFERENCES Zulieferer(Unternehmensname)
                                                 ON DELETE CASCADE
                                                 ON UPDATE CASCADE,
                                               FOREIGN KEY (Unternehmensnamen2) REFERENCES Zulieferer(Unternehmensname)
                                                 ON DELETE CASCADE
                                                 ON UPDATE CASCADE);






CREATE TABLE Zulieferer(Email VARCHAR(80) NOT NULL COLLATE NOCASE CHECK(length(Email)>0 AND Email LIKE "%@%.%" AND
                        substr(Email,1,(instr(Email,"@")-1)) NOT GLOB "*[^A-Za-z0-9]*" AND
                        substr(Email,(instr(Email,"@")+1),(instr(Email,".")-instr(Email,"@")-1))NOT GLOB "*[^A-Za-z0-9]*" AND
                        substr(Email,(instr(Email,".")+1))NOT GLOB "*[^A-Za-z]*"),
                        Unternehmensname VARCHAR(80) NOT NULL CHECK(Unternehmensname NOT GLOB "*[^A-Za-z]*" AND length(Unternehmensname)>0),
                         PRIMARY KEY (Email),
                         FOREIGN KEY (Email) REFERENCES Nutzer(Email)
                         ON DELETE CASCADE
                         ON UPDATE CASCADE);
sqlsqlite


Solution 1:[1]

Master table should be created before detail table. Why? Because detail table's column references master table's primary key. If master table doesn't exist yet, there's nothing to reference.

Furthermore, as I said, foreign key is supposed to reference a primary key in another table. You're referencing columns that aren't primary key, so - that won't work.

Solution 2:[2]

You need to create the table first before using it as foreign key

CREATE TABLE Zuliefererkooperiertmitzulieferer(Unternehmensnamen1 VARCHAR(80) NOT NULL CHECK(Unternehmensnamen1 NOT GLOB "[^A-Za-z]" AND length(Unternehmensnamen1)>0), Unternehmensnamen2 VARCHAR(80) NOT NULL CHECK(Unternehmensnamen2 NOT GLOB "[^A-Za-z]" AND length(Unternehmensnamen2)>0 AND (Unternehmensnamen1 NOT LIKE Unternehmensnamen2)), PRIMARY KEY (Unternehmensnamen1,Unternehmensnamen2), FOREIGN KEY (Unternehmensnamen1) REFERENCES Zulieferer(Unternehmensname) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Unternehmensnamen2) REFERENCES Zulieferer(Unternehmensname) ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE Zulieferer(Email VARCHAR(80) NOT NULL COLLATE NOCASE CHECK(length(Email)>0 AND Email LIKE "%@%.%" AND substr(Email,1,(instr(Email,"@")-1)) NOT GLOB "[^A-Za-z0-9]" AND substr(Email,(instr(Email,"@")+1),(instr(Email,".")-instr(Email,"@")-1))NOT GLOB "[^A-Za-z0-9]" AND substr(Email,(instr(Email,".")+1))NOT GLOB "[^A-Za-z]"), Unternehmensname VARCHAR(80) NOT NULL CHECK(Unternehmensname NOT GLOB "[^A-Za-z]" AND length(Unternehmensname)>0), PRIMARY KEY (Email), FOREIGN KEY (Email) REFERENCES Nutzer(Email) ON DELETE CASCADE ON UPDATE CASCADE);'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot
Solution 2 Jens

Related Questions