Syntax Error near ScanID

Question

Evening all

I have been working on a small application but kind of stuck at the SQLite foreign key constraint. Basically what i have is one "HostLookuptable" as

CREATE TABLE tblHostLookup ( 
HostID INTEGER PRIMARY KEY AUTOINCREMENT, 
HostName TEXT);

And one "ScanLookuptable" as

CREATE TABLE tblScanLookup ( 
ScanID INTEGER PRIMARY KEY AUTOINCREMENT, 
ScanDate TEXT);

Then there is another table that will have mapping between two tables as "ScanHistorytable"

CREATE TABLE tblScanHistory (
ScanHistoryID INTEGER PRIMARY KEY AUTOINCREMENT,
HostID INTEGER,
FOREIGN KEY(HostID) REFERENCES tblHostLookup(HostID),
ScanID INTEGER,
FOREIGN KEY(ScanID) REFERENCES tblScanLookup(ScanID));

But i keep getting an error saying

Syntax error near ScanID

Why so? Are we not allowed to have more than one foreign key in a table? Any help in this regard would be great.

Thanks

Answer 1

You cannot mix table columns and table constraints; the constraints must be listed after all the columns:

CREATE TABLE tblScanHistory (
    ScanHistoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    HostID INTEGER,
    ScanID INTEGER,
    FOREIGN KEY(HostID) REFERENCES tblHostLookup(HostID),
    FOREIGN KEY(ScanID) REFERENCES tblScanLookup(ScanID)
);

Or, simpler:

CREATE TABLE tblScanHistory (
    ScanHistoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    HostID INTEGER REFERENCES tblHostLookup(HostID),
    ScanID INTEGER REFERENCES tblScanLookup(ScanID)
);