SQLITE使用FOREIGN键和Composite键创建表

Question

我是安卓sqlite数据库的新手，所以我不知道用主键和外键编写这个create语句的正确语法是什么。下面我将跳过我的列名并创建table语句。我希望有人能引导我在这件事上做错了什么。

//table names
public static final String TABLE_configuracao_situacao = "configuracao_situacao";
public static final String TABLE_situacao = "situacao";
//column names for Situation table
public static final String KEY_idSituacao = "id_situacao";
public static final String KEY_descicao = "descricao";
public static final String KEY_padrao = "padrao";
public static final String KEY_button = "button";
//column names for configuracao_situacao table
public static final String KEY_situacao = "situacao";
public static final String KEY_configuracao = "configuracao";
public static final String KEY_minimo = "minimo";
public static final String KEY_maximo = "maximo";
public static final String KEY_acaoMin = "acao_min";
public static final String KEY_acaoMax = "acao_max";
public static final String KEY_dataCriacao = "data_criacao";
//Table created statement for situation
public static final String DATABASE_CREATE_SITUACAO =
        "CREATE TABLE " + TABLE_situacao 
        + "("   +   KEY_idSituacao  + " integer primary key autoincrement, "
                +   KEY_descicao    + " string , "
                +   KEY_padrao      + " string, "
                +   KEY_button      + " integer "
        + ");";
//Table created statement configuracao_situacao
public static final String DATABASE_CREATE_Table_CONFIGSITUACAO =
        "CREATE TABLE " +   TABLE_configuracao_situacao
        + "("   +   KEY_situacao        + " integer primary key, "
                +   KEY_configuracao    + " integer primary key, "
                +   KEY_minimo          + " text, "
                +   KEY_maximo          + " text, "
                +   KEY_acaoMin         + " text, "
                +   KEY_acaoMax         + " text, "
                +   KEY_dataCriacao     + " DATETIME, "
                "FOREIGN KEY (" + KEY_situacao + " ) REFERENCES " +  TABLE_situacao + "(" + KEY_idSituacao + ")"
        + ");";

问题出在configuracao_situacao-create语句中，同时生成foregin键。有人能告诉我如何定义这里的钥匙吗。提前谢谢。

Answer 1

您使用了错误的数据类型检查以下代码，您将获得参考

//Table created statement for situation
public static final String DATABASE_CREATE_SITUACAO =
    "CREATE TABLE " + TABLE_situacao 
    + "("   +   KEY_idSituacao  + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            +   KEY_descicao    + " TEXT, "
            +   KEY_padrao      + " TEXT, "
            +   KEY_button      + " INTEGER"
    + ");";
//Table created statement configuracao_situacao
public static final String DATABASE_CREATE_Table_CONFIGSITUACAO =
    "CREATE TABLE " +   TABLE_configuracao_situacao
    + "("   +   KEY_situacao        + " INTEGER PRIMARY KEY, "
            +   KEY_configuracao    + " INTEGER PRIMARY KEY, "
            +   KEY_minimo          + " TEXT,"
            +   KEY_maximo          + " TEXT,"
            +   KEY_acaoMin         + " TEXT,"
            +   KEY_acaoMax         + " TEXT,"
            +   KEY_dataCriacao     + " TEXT,"
            "FOREIGN KEY (" + KEY_situacao + " ) REFERENCES " +  TABLE_situacao + "(" + KEY_idSituacao + ")"
    + ");";

Answer 2

也许在DATETIME:之后有多余的逗号

+KEY_dataCriacao+"DATETIME"

SQLite没有类型字段DATETIME和字符串

https://www.sqlite.org/datatype3.html