Why am I getting a foreign key mismatch when trying to insert unique values from parent table into child?

I am attempting to insert the unique values of the Subject field in Main_Standards into the Name field of Subjects, which is a foreign key referencing the Subject field.

Here is my Main_Standards table:

CREATE TABLE "Main_Standards" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "Subfield"  TEXT NOT NULL,
    "Domain"    TEXT NOT NULL,
    "Code"  INTEGER NOT NULL UNIQUE,
    "Standard_Type" TEXT NOT NULL,
    "Assessment_Type"   TEXT NOT NULL,
    "Subject"   TEXT,
    "Subject_Reference" TEXT,
    "Title" TEXT NOT NULL,
    "Level" INTEGER NOT NULL,
    "Credits"   INTEGER NOT NULL,
    "Grading_Scheme"    TEXT NOT NULL,
    "Status"    TEXT NOT NULL,
    "Expiry_Date"   TEXT,
    "Publication_Date"  TEXT NOT NULL,
    "CurrentVersion"    INTEGER NOT NULL,
    "Hyperlink" TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
)

And here is my Subjects table.

CREATE TABLE "Subjects" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "Name"  TEXT NOT NULL UNIQUE,
    "Subfield"  TEXT NOT NULL,
    "Selected"  TEXT NOT NULL DEFAULT 'False',
    FOREIGN KEY("Name") REFERENCES "Main_Standards"("Subject"),
    PRIMARY KEY("ID" AUTOINCREMENT)
)

This is the query resulting in the error thrown:

INSERT OR IGNORE INTO Subjects(Name, Subfield)
SELECT DISTINCT
Subject, 
Subfield
FROM Main_Standards
WHERE Standard_Type = 'A';

The error itself:

Execution finished with errors.
Result: foreign key mismatch - "Subjects" referencing "Main_Standards"
At line 1:
INSERT OR IGNORE INTO Subjects(Name)
SELECT DISTINCT
Subject
FROM Main_Standards
WHERE Standard_Type = 'A';

Solution

From the documentation:

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.

Main_Standards.Subject is not the PK of the table, nor do you have anything to suggest there's a unique index on it. Thus, an error when you try to use it as a parent key.

Unrelated, but PRIMARY KEYs are, by definition, unique. You don't need to also specify UNIQUE with them. Doing so is a pessimization, as it creates a redundant index that has to be updated every time the table is modified.