I'm trying to create Two Tables in sql.js but there is an error regarding foreign key constraint. there is no problem in creating the first table

Question

Sample Code

<script>
config = {
    locateFile: filename => `sql-wasm.wasm`
  }
initSqlJs(config).then(function(SQL)
{
    //Create the database
    const db = new SQL.Database();

    //create first table - Questions (Parent Table)
    let question = "CREATE TABLE Questions(qid INT NOT NULL, qdesc CHAR, PRIMARY KEY(qid));\
        INSERT INTO Questions VALUES(1, 'set1');\
        INSERT INTO Questions VALUES(2, 'set2');\
        INSERT INTO Questions VALUES(3, 'set3');\
        INSERT INTO Questions VALUES(4, 'set4');\
        INSERT INTO Questions VALUES(5, 'set5');";
    db.run(question);
    const result = db.exec("SELECT * FROM Questions");
    console.log(result);
    
    //create second Table - Qimages (Child Table)
    let qimage = "CREATE TABLE Qimages(img_id INT AUTO_INCREMENT, imgurl VARCHAR,FOREIGN KEY (qid) REFERENCES Questions(qid));\
        INSERT INTO Qimages VALUES('https://www.w3schools.com/howto/img_woods.jpg',1);\
        INSERT INTO Qimages VALUES('https://www.w3schools.com/howto/img_5terre.jpg',1);\
        INSERT INTO Qimages VALUES('https://www.w3schools.com/howto/img_mountains.jpg',2);";
    db.run(qimage);
    const res = db.exec("SELECT * FROM Qimages");
    console.log(res);
</script>

Below is the Error I'm Getting

uncaught (in promise) Error: unknown column "qid" in foreign key definition

Exactly as the message says, your second table (`Qimages`) has no `qid` column defined. — Nick, Mar 27 '22 at 05:41
@MikeT answer solved the problem and you were right too. Thanks Nick. — iakshayd, Mar 27 '22 at 11:52

score 0 · Accepted Answer · answered Mar 27 '22 at 07:26

0

As the message says, there is no qid column in the Qimages table. I suspect that you want:-

CREATE TABLE Qimages(img_id INT AUTO_INCREMENT, imgurl VARCHAR,qid INTEGER REFERENCES Questions(qid));

Thus the value of the qid column (now defined), that uses the column level version of the Foreign Key constraint declaration, must have a corresponding value in the qid column of the Question table.

Alternately you could use:-

CREATE TABLE Qimages(img_id INT AUTO_INCREMENT, imgurl VARCHAR,qid INTEGER, FOREIGN KEY (qid) REFERENCES Questions(qid));

answered Mar 27 '22 at 07:26

MikeT

51,415
16
49
68

this worked but somehow AUTO_INCREMENT is not working so have to manually enter img_id values. anyways thanks a ton @MikeT I'll look into it. – iakshayd Mar 27 '22 at 11:39
@iakshayd AUTOINCREMENT (no underscore) only works if INTEGER is used along with the column being the PRIMARY KEY. So `img_id INTEGER AUTOINCREMENT PRIMARY KEY`. However, you will get very much the same (a generated img_id) using `img_id INTEGER PRIMARY KEY` without the overheads of coding AUTOINCREMENT. See https://sqlite.org/autoinc.html – MikeT Mar 27 '22 at 12:09
oh i got it now.@MikeT – iakshayd Mar 28 '22 at 04:40

I'm trying to create Two Tables in sql.js but there is an error regarding foreign key constraint. there is no problem in creating the first table

1 Answers1