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?

score 16 · Accepted Answer · answered Jan 09 '13 at 09:14

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.

score 2 · Answer 2 · answered Jan 09 '13 at 02:39

2

you have a typo error on declaring the foreign key

FOREIGN KEY (STUDENITD)

should be

FOREIGN KEY (STUDENTID)

answered Jan 09 '13 at 02:39

John Woo

258,903
69
498
492

Thanks for notifying me of the typo. Sorry about that, but after changing it, it's still not working :/ – Chris Bennett Jan 09 '13 at 03:21

score 2 · Answer 3 · answered Jan 09 '13 at 08:54

2

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

answered Jan 09 '13 at 08:54

CL.

173,858
17
217
259

This one is still not correct: the foreign key name should not be in parentheses. Also, you're naming the foreign key after the field name, which is not good practice. – Martijn Jun 25 '13 at 15:30
...and finally, when defining a foreign key inline, you must omit the `FOREIGN KEY constraint-name` part, and just specify `REFERENCES table(field)`. – Martijn Jun 26 '13 at 09:00

in SQLite how to implement a many to many relationship

3 Answers3

Linked