Foreign Key Error in SQLite3 CREATE TABLE Command

Question

Looking for fresh eyes on this, I'm running the command in sqlite3 interactive and can't see the problem. Here's the code:

CREATE TABLE Personas(
    PersonID INTEGER NOT NULL PRIMARY KEY,
    FirstNameAtBirth    TEXT,
    LastNameAtBirth     TEXT,
    MidNameAtBirth      TEXT,
    discordID           TEXT,
    githubID            TEXT
);

^ Executes successfully,

CREATE TABLE Conversations(
    ConvoID INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY(PersonID) REFERENCES Personas(PersonID),
    BodyText            TEXT,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    Reactions           TEXT
);

^ fails with Error: in prepare, unknown column "PersonID" in foreign key definition (1)
I'm in an active database, I've set PRAGMA foreign_keys=ON; prior to any transactions, any idea where the problem is??

Answer 1

You need also the column PersonID in the table conversations, as it the value of the reference and the Foreign keyis only a constraint for the column PersonID.

It indicates that you can add only PersonIds that exists in Personas

CREATE TABLE Personas(
    PersonID INTEGER NOT NULL PRIMARY KEY,
    FirstNameAtBirth    TEXT,
    LastNameAtBirth     TEXT,
    MidNameAtBirth      TEXT,
    discordID           TEXT,
    githubID            TEXT
);

✓

CREATE TABLE Conversations(
    ConvoID INTEGER NOT NULL PRIMARY KEY,
  PersonID INTEGER NOT NULL,
    BodyText            TEXT,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    Reactions           TEXT,
  FOREIGN KEY(PersonID) REFERENCES Personas(PersonID)
);

✓fiddle