this is my student_table create query
CREATE TABLE "student_table" (
"student_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL,
"gender" TEXT NOT NULL,
"year_admited" INTEGER
);
this is for my year_table
CREATE TABLE "year_table" (
"year" INTEGER NOT NULL,
"student_id" INTEGER NOT NULL,
"class" INTEGER NOT NULL,
PRIMARY KEY("year"),
FOREIGN KEY("student_id") REFERENCES "student_table"("student_id")
);
and this for the terms_table
CREATE TABLE "terms_table" (
"year" INTEGER NOT NULL,
"student_id" INTEGER NOT NULL,
"term" INTEGER NOT NULL,
"fees" INTEGER,
PRIMARY KEY("term","year"),
FOREIGN KEY("year") REFERENCES "year_table"("year"),
FOREIGN KEY("student_id") REFERENCES "year_table"("student_id")
);
i successfully inserted a row in the student_table and in the year_table
i tried adding values to the terms table
INSERT INTO "main"."terms_table"
("year", "student_id", "term", "fees")
VALUES (2020, 1, 1, 900000);
but it gave this error
Error adding record. Message from database engine:
foreign key mismatch - "terms_table" referencing "year_table" (INSERT INTO
"main"."terms_table"
("year", "student_id", "term", "fees")
VALUES (2020, 1, 1, 900000);)
I'm using dB browser for SQLite
What am i doing wrong?