Error while creating tables simultaneously in sqlite database

Question

I’m developing a mobile application that saves data from a form in the local database, using the sqlite. Only every time the database is reset or changed its version it returns me an error that could not create the tables. These tables have a relationship One To Many.

It is curious that if I create a table first, commenting on the row that creates the second table, and then uncomment the same one it generates both normally. How to do not have this error, and in the forums I saw, this is the way to create the tables with sqlite.

Creating the tables:

public Conexao(Context context) {
    super(context, name, null, version);
}


@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL("create table paciente(_id integer primary key autoincrement, nome varchar(50), cpf varchar(11), data varchar(15) )");
    db.execSQL("create table imagens(_id integer primary key autoincrement, status varchar(10), imagem varchar(300), FOREIGN KEY(paciente_id) REFERENCES paciente(_id))");
    Log.i("TAG", "Banco criado com sucesso");

}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("drop table paciente");
    db.execSQL("drop table imagens");
    onCreate(db);
}

Inserting a new register:

public long inserirFoto(Foto imagem){

    List<String> fotos = imagem.getImagem();
    ContentValues values = new ContentValues();
    for(int i = 0; i < fotos.size(); i++) {

        values.put("status", "skdoasm");
        values.put("imagem", "skfmsakf");

    }return banco.insert("imagens", null, values);
}

public long inserirPaciente(Paciente paciente){

    ContentValues values = new ContentValues();
    values.put("nome", paciente.getNome());
    values.put("cpf", paciente.getCpf());
    values.put("data", paciente.getData());
    return banco.insert("paciente", null, values);
}

Relationship tables:

public class Paciente implements Serializable {

private int id;
private String nome;
private String cpf;
public  Foto foto;
private String data;
getter e setter...

public class Foto  implements Serializable {

@Expose
private List<String> imagem;
@Expose
private int id;
@Expose
private String status;

public List<String> getImagem() {
    return imagem;
}

getter e setter...

Exit error:

android.database.sqlite.SQLiteException: unknown column "paciente_id" in foreign key definition (code 1): , while compiling: create table imagens(_id integer primary key autoincrement, status varchar(10), imagem varchar(300), FOREIGN KEY(paciente_id) REFERENCES paciente(_id))

Answer 1

Friend, your second table is with wrong script, see:

create table imagens(
  _id integer primary key autoincrement, 
  status varchar(10), 
  imagem varchar(300), 
  FOREIGN KEY(paciente_id) REFERENCES paciente(_id)
)

You say that paciente_id is your foreign key with reference in the table pacientes spine _id. But there isn’t any paciente_id in the declaration of its table.
Change to:

create table imagens(
  _id integer primary key autoincrement, 
  paciente_id integer NOT NULL,
  status varchar(10), 
  imagem varchar(300), 
  FOREIGN KEY(paciente_id) REFERENCES paciente(_id)
)

Thus remaining:

db.execSQL("create table imagens(_id integer primary key autoincrement, paciente_id integer NOT NULL, status varchar(10), imagem varchar(300), FOREIGN KEY(paciente_id) REFERENCES paciente(_id))");