ScanID 附近的语法错误

2022-01-20 00:00:00 foreign-key-relationship sqlite

晚上好

我一直在开发一个小型应用程序，但有点卡在 SQLite 外键约束上.基本上我所拥有的是一个HostLookuptable"

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);

还有一个ScanLookuptable"作为

And one "ScanLookuptable" as

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

然后有另一个表将在两个表之间映射为ScanHistorytable"

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));

但我一直收到错误提示

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.

谢谢

推荐答案

不能混用表列和表约束；约束必须列在所有列之后:

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)
);

或者，更简单:

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

相关文章