生成SQLite脚本

Question

我正在使用Enterprise Architect对数据库建模。现在，我想为SQLite数据库生成一些表，但它们没有实现完整的SQL92。

我的问题是ALTER TABLE Node ADD CONSTRAINT FK_name FOREIGN KEY (field) REFERENCES TABLE_NAME (ID)

是否有好的脚本/模型来生成SQLite脚本?

佩德罗

编辑

PRAGMA foreign_keys = ON;
CREATE TABLE MonitoredData ( 
ID INTEGER PRIMARY KEY NOT NULL,
NODE INTEGER,
MINUTE INTEGER,
HOUR INTEGER,
DAY INTEGER,
MONTH INTEGER,
YEAR INTEGER,
SPAN INTEGER
);
CREATE TABLE Node ( 
ID INTEGER PRIMARY KEY NOT NULL,
MEMORY_USAGE INTEGER,
CPU_USAGE INTEGER,
NETWORK_USAGE INTEGER,
DISK_USAGE INTEGER
FOREIGN KEY(ID) REFERENCES MonitoredData(NODE)
);
CREATE TABLE MemoryUsage ( 
ID INTEGER PRIMARY KEY NOT NULL,
SIZE INTEGER,
RESIDENT INTEGER,
SHARE INTEGER,
TEXT INTEGER,
DATA INTEGER,
VIRTUALSIZE INTEGER,
RSS INTEGER,
RSSLIM TEXT,
MEM_TOTAL INTEGER,
MEM_USED INTEGER,
MEM_FREE INTEGER,
MEM_BUFFERS INTEGER,
MEM_CACHED INTEGER
FOREIGN KEY(ID) REFERENCES Node(MEMORY_USAGE)
);

Answer 1

SQLite要求在创建表的同时创建外键约束。来自文档:

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

您还需要在每个连接的基础上启用外键强制(由于历史原因):

PRAGMA foreign_keys = ON;