在 SQLite 中如何实现多对多关系 [英] in SQLite how to implement a many to many relationship

问题描述

我正在制作一个具有多对多关系的 SQLite 数据库，我已使用以下代码将其分解为两个一对多关系

I am making an SQLite database that has a many to many relationship I have broken this up into two one to many relationships with the following code

SQLiteManager: CREATE  TABLE "main"."LESSONS" 
(
   "LESSONID" INTEGER PRIMARY KEY  
   NOT NULL , "MODULEID" INTEGER, FOREIGN KEY(MODULEID) REFERENCES MODULES 
   (MODULEID), "STUDENTID" INTEGER, FOREIGN KEY (STUDENITD) REFERENCES STUDENTS
   (STUDENTID)
)    

错误:

[near ""STUDENTID"": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE)
[mozIStorageConnection.createStatement]

有谁知道我该如何解决这个错误?

does anyone know how I can fix this error?

推荐答案

查看文档；它会告诉你，如果你在字段定义本身上指定了一个外键，你不应该使用关键字 FOREIGN KEY 本身.此外，正如 CL. 所指出的，您使用了过多的逗号，即使单独指定约束时，外键名称也不应放在括号中.

Check out the documentation; it’ll show you that if you specify a foreign key on the field definition itself, you shouldn’t use the keyword FOREIGN KEY itself. Also, you used too many commas, as pointed out by CL., and even when specifying the constraint separately, the foreign key name should not be in parentheses.

此语句满足您的要求:

CREATE TABLE "LESSONS"
(
    "LESSONID"  INTEGER PRIMARY KEY NOT NULL,
    "MODULEID"  INTEGER REFERENCES MODULES(MODULEID),
    "STUDENTID" INTEGER REFERENCES STUDENTS(STUDENTID)
);

还要注意的是，如果MODULEID是MODULES表的主键，那么你就不必指定它本身；SQLite 不需要它.话虽如此，我更喜欢这样指定它，因为其他 SQL 引擎确实期望它.

Also note that, if MODULEID is the primary key of table MODULES, then you don’t have to specify it, per se; SQLite doesn’t require it. That being said, I prefer specifying it like this since other SQL engines do expect it.

这篇关于在 SQLite 中如何实现多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！