Getting exception Error(near "INDEX": syntax error) in C#

Question

I'm a newbie in Database. I'm trying to create some tables but it fails with error "near 'INDEX' : syntax error". Here is my codes to generate tables :

        try {
        using( SqliteCommand cmd = _db.CreateCommand() ) {

            // create words table
            cmd.CommandText = "DROP TABLE words";
            cmd.ExecuteNonQuery();
            cmd.CommandText = 
                "CREATE TABLE IF NOT EXISTS words(" +
                    "word_id INTEGER AUTO_INCREMENT," +
                    "word_eng VARCHAR(128)," +
                    "PRIMARY KEY(word_id)" +
                    ")";
            cmd.ExecuteNonQuery();

            // create word_classes table
            cmd.CommandText = "DROP TABLE word_classes";
            cmd.ExecuteNonQuery();
            cmd.CommandText = 
                "CREATE TABLE IF NOT EXISTS word_classes(" +
                    "word_class_id INTEGER," +
                    "word_class_name VARCHAR(50)," +
                    "PRIMARY KEY(word_class_id)" +
                    ")";
            cmd.ExecuteNonQuery();

            // create meanings table
            cmd.CommandText = "DROP TABLE meanings";
            cmd.ExecuteNonQuery();
            cmd.CommandText = 
                "CREATE TABLE IF NOT EXISTS meanings(" +
                    "meaning_id INTEGER AUTO_INCREMENT," +
                    "meaning VARCHAR(100)," +
                    "word_class_id INTEGER," +
                    "PRIMARY KEY(meaning_id)," +
                    "FOREIGN KEY(word_class_id) REFERENCES word_classes(word_class_id)" + 
                    ")";
            cmd.ExecuteNonQuery();

            // create word_meaning_realationship table
            cmd.CommandText = "DROP TABLE word_meaning_realationship";
            cmd.ExecuteNonQuery();
            cmd.CommandText =
                "CREATE TABLE IF NOT EXISTS word_meaning_realationship(" +
                    "word_id INTEGER," +
                    "meaning_id INTEGER," +
                    "FOREIGN KEY(word_id) REFERENCES words(word_id)" +
                    "FOREIGN KEY(meaning_id) REFERENCES meanings(meaning_id)" +
                    ")";
            cmd.ExecuteNonQuery();

            // create decks table
            cmd.CommandText = "DROP TABLE decks";
            cmd.ExecuteNonQuery();
            cmd.CommandText =
                "CREATE TABLE IF NOT EXISTS decks(" +
                    "deck_id INTEGER AUTO_INCREMENT," +
                    "deck_name VARCHAR(128)," +
                    "deck_version INTEGER," +
                    "PRIMARY KEY(deck_id)" +
                    ")";
            cmd.ExecuteNonQuery();

            // create deck_word_relationship table
            cmd.CommandText = "DROP TABLE deck_word_relationship";
            cmd.ExecuteNonQuery();
            cmd.CommandText =
                "CREATE TABLE IF NOT EXISTS deck_word_relationship(" +
                    "deck_id INTEGER," +
                    "word_id INTEGER," +
                    "FOREIGN KEY(deck_id) REFERENCES decks(deck_id)," +
                    "FOREIGN KEY(word_id) REFERENCES words(word_id)" +
                    ")";
            cmd.ExecuteNonQuery();
        }
    } catch( SqliteException ex ) {
        Debug.LogError( "DBError : " + ex.ToString() );
    }

It would be very appreciated if you check my other codes to create table, as I'm a totally newbie in database and mysql, and am not very confident of my codes :)

** updated ** I decided not to think about indexing for now. I updated my codes that have small errors that made my code not working. But if you tell me how to apply indexing for me, I will make it as a correct answer after application. Thanks for helps of you guys.

I thought they are the same;aren't they? Isn't Sqlite based on mysql? — hanstar17
– hanstar17, Commented Feb 9, 2014 at 13:36
if you think so, run the statement on ide like sqliteadmin. there are many syntax changes between mysql and Sqlite — Damith
– Damith, Commented Feb 9, 2014 at 13:45

Kevin Brechbühl · Accepted Answer · 2014-02-09 11:54:36Z

1

You have different errors in your statements:

Spelling of the word REFERENCES for foreign keys. You always wrote this with double "F", this is wrong. Don't know if this is a typo.
In creation of table meaning, you have missed a NOT for checking if table exists: this should be CREATE TABLE IF NOT EXISTS
In creation of table deck, you want to create an index on the field text, but this field does not exists. So you should create the index on the field name or something different

After fixing all this errors, I could execute all of statements on a plain MySQL database (did not test your code, just tested the statements directly on the database).

edited Feb 9, 2014 at 11:54

answered Feb 9, 2014 at 11:36

Kevin Brechbühl

4,7153 gold badges26 silver badges47 bronze badges

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

4 Comments

hanstar17 Over a year ago

Still the error exists though. The error is issued for the first create table query and i don't know why.

hanstar17 Over a year ago

Oh buy the way, the same query works in mysql bench but not in C# sqlite

Kevin Brechbühl Over a year ago

My answer is based on your MySQL tag (which you have fixed actually). Anyway I think that my suggestions are also ok für you :)

hanstar17 Over a year ago

Thank you I applied all the small errors you suggested. But about indexing, I simply took out indexing parts from my code. It was about optimization and I didn't want to think about it now. But I appreciate your help man :) I updated my code as well.

Damith · Accepted Answer · 2014-02-09 13:07:48Z

0

try to create Table first and then add the index like below

cmd.CommandText = @"CREATE TABLE IF NOT EXISTS word(" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "word VARCHAR(128))";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX IF NOT EXISTS idx_word_word ON word(id)";
cmd.ExecuteNonQuery();

answered Feb 9, 2014 at 13:07

Damith

63.2k13 gold badges106 silver badges154 bronze badges

1 Comment

hanstar17 Over a year ago

Thanks man! I 'll try that tomorrow mornin g and let you know!( 12 am in my country )

Collectives™ on Stack Overflow

Getting exception Error(near "INDEX": syntax error) in C#

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related