列不SQLITE3表中创建 [英] 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.

我SQLiteOpenHelper创建三个表：

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

三个表的CREATE语句如下：

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

现在的问题是显然 FeedHash 在 IRFeeds 列不创建而另一些。我期待在sqlite3的命令提示符下输出;

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

这也列出了 FeedHash 列 IRFeeds 。然而，当我执行

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

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

其他所有列不给这样的错误。这种情况引起我的code失败，意外地好。我能会丢失？

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.

推荐答案

奋斗了整整一天后，我设法找出为什么这个问题会触发。不过不知道为什么，但它确实解决问题。这些问题的发生是因为下表这对其他两个表的外键：

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.

这篇关于列不SQLITE3表中创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！