Column not created in SQLITE3 table

问题:

Now I have a weird problem, I've done all kinds of test and I believe I'm seeing something weird.

I create three tables in SQLiteOpenHelper:

public void onCreate(SQLiteDatabase db) {
    try {
        db.execSQL(TABLE_CHANNELS_CREATE);
        db.execSQL(TABLE_FEEDS_CREATE);
        db.execSQL(TABLE_FEEDMAP_CREATE);
    }
    catch (SQLiteException e){
        Toast.makeText(mContext, e.getMessage(), Toast.LENGTH_LONG).show();
    }
}

The CREATE statements for the three tables follow:

CREATE TABLE IF NOT EXISTS IRChannels (
    ChannelId INTEGER PRIMARY KEY, 
    ChannelHash TEXT NOT NULL, 
    ChannelTitle TEXT NOT NULL, 
    ChannelDesc TEXT, ChannelLink TEXT);

CREATE TABLE IF NOT EXISTS IRFeeds (
    FeedId INTEGER PRIMARY KEY, 
    FeedHash TEXT NOT NULL, 
    FeedTitle TEXT NOT NULL, 
    FeedDescription TEXT, 
    FeedLink TEXT);

CREATE TABLE IF NOT EXISTS IRFeedMap (
    ChannelHash_FK TEXT NOT NULL, 
    FeedHash_FK TEXT NOT NULL, 
    FOREIGN KEY (ChannelHash_FK) REFERENCES IRChannels (ChannelHash), 
    FOREIGN KEY (FeedHash_FK) REFERENCES IRFeeds (FeedHash));

The problem is apparently the column FeedHash in IRFeeds is not created while others are. I'm looking at the output in sqlite3 command prompt;

sqlite> .schema
CREATE TABLE IRChannels (
    ChannelId INTEGER PRIMARY KEY, 
    ChannelHash TEXT NOT NULL, 
    ChannelTitle TEXT NOT NULL, 
    ChannelDesc TEXT,         
    ChannelLink TEXT);
CREATE TABLE IRFeedMap (
    ChannelHash_FK TEXT NOT NULL, 
    FeedHash_FK TEXT NOT NULL, 
    FOREIGN KEY (ChannelHash_FK) REFERENCES IRChannels (ChannelHash), 
    FOREIGN KEY (FeedHash_FK) REFERENCES IRFeeds (FeedHash));
CREATE TABLE IRFeeds (
    FeedId INTEGER PRIMARY KEY, 
    FeedHash TEXT NOT NULL, 
    FeedTitle TEXT NOT NULL, 
    FeedDescription TEXT, 
    FeedLink TEXT);

This does list the FeedHash column in IRFeeds. However, when I execute

sqlite> select * from IRFeeds where FeedHash='';
SQL error: no such column: FeedHash

All other columns do not give such errors. This condition is causing my code to fail unexpectedly as well. What could I be missing?

sqlite> select * from IRFeeds where FeedID=1;
sqlite> select * from IRFeeds where FeedTitle='';
sqlite> select * from IRFeeds where FeedDescription='';
sqlite> select * from IRFeeds where FeedLink='';

No errors above when I execute select statement for other columns.

回答1:

There is no error in your SQL. I tested and everything was created properly. Also your SQL query did not cause no such column error. So try to delete the database with context.deleteDatabase(databaseName); and try again.

回答2:

After an entire day of struggle, I managed to isolate why the problem triggers. Still don't know why, but it does fix the problem. The problems occurs because of the following table which has foreign keys on the other two tables:

CREATE TABLE IRFeedMap (
    ChannelHash_FK TEXT NOT NULL, 
    FeedHash_FK TEXT NOT NULL, 
    FOREIGN KEY (ChannelHash_FK) REFERENCES IRChannels (ChannelHash), 
    FOREIGN KEY (FeedHash_FK) REFERENCES IRFeeds (FeedHash));

Changing the column names of foreign key columns to be the same as the column they reference fixes the problem. I changed the statement above to:

CREATE TABLE IRFeedMap (
    ChannelHash TEXT NOT NULL, 
    FeedHash TEXT NOT NULL, 
    FOREIGN KEY (ChannelHash) REFERENCES IRChannels (ChannelHash), 
    FOREIGN KEY (FeedHash) REFERENCES IRFeeds (FeedHash));

And voila! Sanity was restored. Beats me.

回答3:

In my case I was using a SQLite reserved word (column, that was)

I ended up in this SO question, so maybe it helps others in my situation