web sql database foreign key support

问题:

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?

回答1:

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

回答2:

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!

回答3:

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.