What causes a foreign key mismatch error in sqlite database

Question

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?

Answer 1

From Required and Suggested Database Indexes:

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

What you are doing wrong in your code is in the CREATE statement of terms_table:

FOREIGN KEY("student_id") REFERENCES "year_table"("student_id")

where you define the column student_id to reference the column student_id in year_table for which there is no UNIQUE constraint or index.
The column student_id in year_table just references the column student_id of student_table.

What you can do is define the column student_id of terms_table to reference the column student_id in student_table, which makes sense:

FOREIGN KEY("student_id") REFERENCES "student_table"("student_id")

See the demo.