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

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

CodePudding user response：

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);'

CodePudding user response：

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.