in SQLite how to implement a many to many relationship

Question

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

Error:

[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?

Answer 1

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.

This statement does what you want:

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

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.

Answer 2

you have a typo error on declaring the foreign key

FOREIGN KEY (STUDENITD)

should be

FOREIGN KEY (STUDENTID)

Answer 3

You have too many commas. Column attributes are not separated by commas; use them only between table columns:

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