Database table not being created using SQLitePorter - Syntax Error

Question

So i'm having an issue with creating a specific table using the SQLitePorter plugin.

I have a file in my assets folder called initDB.sql, within this i have this SQL Line:

CREATE TABLE IF NOT EXISTS `localListItems` (
   `id` INTEGER NOT NULL ,
   `quantity` TINYINT(2) NOT NULL , 
   `checked` TINYINT(1) NOT NULL DEFAULT 0 ,
   `listId` INTEGER NOT NULL , 
   `itemId` INTEGER NOT NULL , 
    PRIMARY KEY (`id`), 
    KEY `fkIdx_26` (`listId`), 
    CONSTRAINT `FK_26` 
      FOREIGN KEY `fkIdx_26` (`listId`) 
      REFERENCES `localLists` (`id`) 
      ON DELETE CASCADE, 
    KEY `fkIdx_30` (`itemId`), 
    CONSTRAINT `FK_30` 
      FOREIGN KEY `fkIdx_30` (`itemId`) 
      REFERENCES `localItems` (`id`) 
      ON DELETE CASCADE
);

Minified Version:

CREATE TABLE IF NOT EXISTS `localListItems` ( `id` INTEGER NOT NULL , `quantity` TINYINT(2) NOT NULL , `checked` TINYINT(1) NOT NULL DEFAULT 0 , `listId` INTEGER NOT NULL , `itemId` INTEGER NOT NULL , PRIMARY KEY (`id`), KEY `fkIdx_26` (`listId`), CONSTRAINT `FK_26` FOREIGN KEY `fkIdx_26` (`listId`) REFERENCES `localLists` (`id`) ON DELETE CASCADE, KEY `fkIdx_30` (`itemId`), CONSTRAINT `FK_30` FOREIGN KEY `fkIdx_30` (`itemId`) REFERENCES `localItems` (`id`) ON DELETE CASCADE);

which is minified in the document and therefore takes up one line to prevent escaping errors '\n'

I am calling this file using the following typescript code:

this.httpClient.get('assets/initDB.sql', { responseType: 'text' })
      .subscribe((sql:any) => {
        this.sqlitePorter.importSqlToDb(this.database, sql)
          .then(data => {
            this.databaseReady.next(true);
            this.storage.set('database_filled', this.databaseVersionControl);
          })
          .catch(e => console.log(e));
      });

All the other tables are inserting as required (not shown in the above) however the SQL above works fine in PHPMyAdmin but is not working when trying to create it using SQLite.

The error appearing in the console is the following JSON.

{
  "code":5,
  "message":"Failed to import SQL; message=near \"KEY\": syntax error",
  "statement":"CREATE TABLE IF NOT EXISTS `localListItems` ( `id` INTEGER NOT NULL , `quantity` TINYINT(2) NOT NULL , `checked` TINYINT(1) NOT NULL DEFAULT 0 , `listId` INTEGER NOT NULL , `itemId` INTEGER NOT NULL , PRIMARY KEY (`id`), KEY `fkIdx_26` (`listId`), CONSTRAINT `FK_26` FOREIGN KEY `fkIdx_26` (`listId`) REFERENCES `localLists` (`id`) ON DELETE CASCADE, KEY `fkIdx_30` (`itemId`), CONSTRAINT `FK_30` FOREIGN KEY `fkIdx_30` (`itemId`) REFERENCES `localItems` (`id`) ON DELETE CASCADE)"
}

Any help as to why this isn't working would be much appriciated. As i can't understand the error code much.

Thank you.

Matt

DaveAlden · Accepted Answer · 2018-06-06 11:08:54Z

2

Your SQL statement is using MySQL syntax but you are attempting to import it to a SQLite database.

For example, KEY is a MySQL keyword; its SQLite equivalent is INDEX (see here).

In SQLite, the index can't be created in the CREATE TABLE statement, so requires a separate CREATE INDEX statement, e.g.

CREATE INDEX fkIdx_30 ON localListItems(itemId)

You can paste your SQL into an online tool such as http://sqlfiddle.com, set the engine to SQLite and hit "Build Schema" to test the syntax works in SQLite.

answered Jun 6, 2018 at 11:08

DaveAlden

30.4k11 gold badges96 silver badges157 bronze badges

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

1 Comment

Matthew M Over a year ago

Perfect, i can't believe it was something so simple, it has been driving me crazy over the last day! Thanks for your help Dave.

Collectives™ on Stack Overflow

Database table not being created using SQLitePorter - Syntax Error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related