Troubleshooting sqlite3 "syntax" error that doesnt exist?;

December 10, 2022

I’ve currently been trying to troubleshoot what I’ve been doing wrong with this code:

CREATE TABLE IF NOT EXISTS REfs (
headline_id INTEGER NOT NULL,
FOREIGN KEY(headline_id) REFERENCES Headlines(id),
wb_id INTEGER,
FOREIGN KEY(wb_id) REFERENCES Weighted_Biases(id),
url_id INTEGER NOT NULL,
FOREIGN KEY(url_id) REFERENCES Urls(id));

The error I get is:

(1) near "wb_id": syntax error in "
CREATE TABLE IF NOT EXISTS REfs (
headline_id INTEGER NOT NULL,
FOREIGN KEY(headline_id) REFERENCES Headlines(id),
wb_id INTEGER,
FOREIGN KEY(wb_id) REFERENCES Weighted_Biases(id),
url_id INTEGER NOT NULL,
FOREIGN KEY(url_id) REFERENCES Urls(id));"

Am I missing something very basic? I am running this code in the sqlite3 shell and also in sqlitebrowser.

The rest of the code is as follows:

DROP TABLE IF EXISTS Urls;
DROP TABLE IF EXISTS Weighted_Biases;
DROP TABLE IF EXISTS Headlines;
DROP TABLE IF EXISTS REfs;

CREATE TABLE IF NOT EXISTS Urls (
id INTEGER PRIMARY KEY,
url TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS Weighted_Biases (
id INTEGER PRIMARY KEY,
wb INTEGER NOT NULL);

CREATE TABLE IF NOT EXISTS Headlines (
id INTEGER PRIMARY KEY,
headline TEXT NOT NULL,
date TEXT NOT NULL);

CREATE TABLE IF NOT EXISTS REfs (
headline_id INTEGER NOT NULL,
FOREIGN KEY(headline_id) REFERENCES Headlines(id),
wb_id INTEGER,
FOREIGN KEY(wb_id) REFERENCES Weighted_Biases(id),
url_id INTEGER NOT NULL,
FOREIGN KEY(url_id) REFERENCES Urls(id));

INSERT INTO Urls (url) VALUES ('https://www.url.com');
INSERT INTO Weighted_Biases (wb) VALUES (5);
INSERT INTO Headlines (headline,date) VALUES ('headline1','10-12-2022');

I am currently learning sqlite3 and database management as a novice, so this code is currently just testing db setup. If I delete the REfs table then everything works fine.

>Solution :

You have to list all fields first:

CREATE TABLE IF NOT EXISTS REfs (
  headline_id INTEGER NOT NULL,
  wb_id INTEGER,
  url_id INTEGER NOT NULL,
  FOREIGN KEY(wb_id) REFERENCES Weighted_Biases(id),
  FOREIGN KEY(headline_id) REFERENCES Headlines(id),
  FOREIGN KEY(url_id) REFERENCES Urls(id)
);