web sql database foreign key support

Question

I'm testing this statement in Safari 5.0.5, but I get an error before FOREIGN:

CREATE TABLE IF NOT EXISTS Idea (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created TIMESTAMP NOT NULL,
    sketchID INTEGER,
    categoryID INTEGER NOT NULL, 
    FOREIGN KEY (sketchID) REFERENCES (Sketch),
    FOREIGN KEY (categoryID) REFERENCES (Category));

I get the following error message:

SQLStatementError 1 [DATABASE] near "(": syntax error

Where is the error in this SQL statement?

Darktalker · Accepted Answer · 2011-11-18 13:32:10Z

9

keeping in mind that even with the correct syntax, foreign key feature is not enabled and could not in web database. because foreign key feature is disabled by default in sqlite3, you have to manually enable it via statement "PRAGMA foreign_keys = ON". Unfortunately, PRAGMA statement is disabled in web database. good luck!

answered Nov 18, 2011 at 13:32

Darktalker

3462 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2011-07-11 08:11:59Z

4

(Adding my comment as an answer)

As Neil pointed out, you are closing the bracket at the wrong position.

Additionally the syntax for the foreign key is wrong, the following should work (provided the HTML5 SQL dialect is standard compliant)

CREATE TABLE IF NOT EXISTS Idea 
(    
   id INTEGER PRIMARY KEY,    
   title TEXT NOT NULL,     
   content TEXT NOT NULL,    
   created TIMESTAMP NOT NULL,    
   sketchID INTEGER,    
   categoryID INTEGER NOT NULL,
   FOREIGN KEY (sketchID) REFERENCES Sketch (sketchId),    
   FOREIGN KEY (categoryID) REFERENCES Category (categoryId)
);

answered Jul 11, 2011 at 8:11

user330315

Comments

Neil Knight · Accepted Answer · 2011-07-11 08:11:19Z

0

You need to replace the ) with a , after categoryID INTEGER NOT NULL) so your statement will become:

CREATE TABLE IF NOT EXISTS Idea (    
           id INTEGER PRIMARY KEY,    
           title TEXT NOT NULL,     
           content TEXT NOT NULL,    
           created TIMESTAMP NOT NULL,    
           sketchID INTEGER,    
           categoryID INTEGER NOT NULL,
           FOREIGN KEY (sketchID) REFERENCES Sketch (sketchID),    
           FOREIGN KEY (categoryID) REFERENCES Category (categoryID));

Note the additional bracket at the end of the statement.

edited Jul 11, 2011 at 8:11

answered Jul 11, 2011 at 7:47

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

5 Comments

user330315 Over a year ago

And it should be REFERENCES Category (categoryID) - at least in standard SQL.

VansFannel Over a year ago

I've edit my question with your suggestion and I get another error.

VansFannel Over a year ago

@a_horse_with_no_name: You are right, I changed REFERENCES clauses, and it works!! Thanks. Now I need an answer to check it as solution!

Neil Knight Over a year ago

@a_horse_with_no_name: Thanks for spotting that.

user330315 Over a year ago

@VannsFannel: I added my comment as an answer

Collectives™ on Stack Overflow

web sql database foreign key support

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related