I'm trying to insert data into related tables, but I've searched many forums and no option works for me, I'm just starting out with this.
The tables are the following:
Table 1:
form CREATE TABLE(
form_id int(11) PRIMARY KEY,
dateCreation date NOT NULL);
tables 2:
create table form_mvdrecinto(
id_mvdrecinto INTEGER PRIMARY KEY AUTOINCREMENT,
fk_form_id int(11) not null,
long varchar(20) not null,
FOREIGN KEY(id_formulario_fk) REFERENCES form(id_form));
In the save method is where I have the problem, specifically in the ContentValues, how the exact statement should be in my case to insert the data in both related tables.
Thank you.
CodePudding user response：
The method is the convenience insert method which simplifies the construction of INSERT SQL statements based upon a ContentValues.
SQL does not allow the insertion of rows into multiple tables. So you cannot insert into both tables in a single use of the insert method. Furthermore the insertion into the form_mvdrecinto requires you to know the value of the foreign key.
First you have various problems with the SQL.
I believe that you need/want something like ():-
CREATE TABLE IF NOT EXISTS form /* TABLE NAME AFTER, NOT BEFORE THE CREATE KEYWORD/CLAUSE */(form_id int(11) PRIMARY KEY, dateCreation date NOT NULL);
CREATE TABLE IF NOT EXISTS form_mvdrecinto(
id_mvdrecinto INTEGER PRIMARY KEY AUTOINCREMENT,
fk_form_id int(11) not null,
long varchar(20) not null,
FOREIGN KEY (fk_form_id /*<<<<< MUST BE A COLUMN IN THE TABLE */) REFERENCES form(form_id /*<<<<< pretty sureyou want to reference form_id column not the non-existent id_form column*/)
);
Assuming What you would do as an example is to have a method based upon:-
void saveFormWithRelatedFormmvdrecinto(long formId, String date, String _long) {
ContentValues cv = new ContentValues();
cv.put("form_id,formId);
cv.put("date",date);
db.insert("form",null,cv);
cv.clear();
cv.put("fk_form_id",formId);
cv.put("long",_long);
db.insert("form_mvdrecinto",null,cv);
}
- obviously db is an SQLiteDatabase.
- due to the ForeignKey constraint the form must be inserted prior to the formmvdrecinto (or formmvdrecintos are inserted).
As the SQLiteDatabase insert Method uses INSERT OR IGNORE ...., the above could be used for an existing form, it would simply ignore inserting the duplicate form and then continue to insert the formmdvrecinto.
It appears that you may have the misconception that FOREIGN KEY builds relationships automatically. It DOES NOT (it cannot) you have to indicate the actual relationship. FOREIGN KEY is a constraint, a rule, that says that the column(s) in the table with the foreign key MUST contain a value that exists in the REFRENECED column(s) in the REFRENCED TABLE otherwise a conflict (failure) will result. INSERT OR IGNORE does not ignore such a conflict.
Demonstration
The following is a working demonstration based upon the tables above. In addition to using a known form id for the insertion of form_mvdrecinto rows (it ads 2 such rows). It also demonstrates a more precarious insertion based upon the dateCreated (if not unique then the form_mvdrecinto inserted will be an arbritary row where the datecreated is matched).
First most of the code is in the DatabaseHelper class it being :-
class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "the_database.db";
public static final int DATABASE_VERSION = 1;
public static final String FORM_TABLE = "form";
public static final String COL_FORM_FORMID = "form_id";
public static final String COL_FORM_DATECREATION = "date_creation";
public static final String FORM_MVDRECINTO_TABLE = "form_mvdrecinto";
public static final String COL_FORMMVDRECINTO_FORMMVDRECINTO_ID = "id_mvdrecinto";
public static final String COL_FORMMVDRECINTO_FK_FORM_ID = "fk_form_id";
public static final String COL_FORMMVDRECINTO_LONG = "long";
private static volatile DatabaseHelper instance = null;
private SQLiteDatabase db;
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME,null, DATABASE_VERSION);
db = this.getWritableDatabase();
/* By defauly Foreign Keys are not turned no, so turn them on */
db.setForeignKeyConstraintsEnabled(true);
}
public static DatabaseHelper getInstance(Context context) {
if (instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE IF NOT EXISTS " FORM_TABLE " ("
COL_FORM_FORMID " INTEGER PRIMARY KEY "
"," COL_FORM_DATECREATION " date NOT NULL"
");"
);
db.execSQL(
"CREATE TABLE IF NOT EXISTS " FORM_MVDRECINTO_TABLE "("
COL_FORMMVDRECINTO_FORMMVDRECINTO_ID " INTEGER PRIMARY KEY"
"," COL_FORMMVDRECINTO_FK_FORM_ID " INTEGER NOT NULL"
"," COL_FORMMVDRECINTO_LONG " TEXT NOT NULL"
", FOREIGN KEY (" COL_FORMMVDRECINTO_FK_FORM_ID ") REFERENCES " FORM_TABLE "(" COL_FORM_FORMID ")"
");"
);
}
public long insertForm(Long id, String dateCreated) {
ContentValues cv = new ContentValues();
if (id != null && id > 0) {
cv.put(COL_FORM_FORMID,id);
}
cv.put(COL_FORM_DATECREATION,dateCreated);
return db.insert(FORM_TABLE,null,cv);
}
public long insertForm(String dateCreated) {
return insertForm(null,dateCreated);
}
public long insertFormMVDRecinto(Long id, long form_id, String _long) {
ContentValues cv = new ContentValues();
if (id != null && id > 0) {
cv.put(COL_FORMMVDRECINTO_FORMMVDRECINTO_ID,id);;
}
cv.put(COL_FORMMVDRECINTO_FK_FORM_ID,form_id);
cv.put(COL_FORMMVDRECINTO_LONG,_long);
return db.insert(FORM_MVDRECINTO_TABLE,null,cv);
}
public long insertFormMVDRecinto(long form_id, String _long) {
return insertFormMVDRecinto(null,form_id,_long);
}
@SuppressLint("Range")
public long insertFormMVDRecinto(Long id, String dateCreated, String _long) {
long rv = -1;
Cursor csr = db.query(FORM_TABLE,new String[]{COL_FORM_FORMID},COL_FORM_DATECREATION "=?",new String[]{dateCreated},null,null,null,"1");
if (csr.moveToFirst()) {
rv = insertFormMVDRecinto(id,csr.getLong(csr.getColumnIndex(COL_FORM_FORMID)),_long);
}
return rv;
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
}
To actually demonstrate the following MainActivity is an activity which :-
- Adds 3 Form rows
- Adds 3 Form_mvdrecinto rows
- The first two being added using the known form_id.
- The last is added according to a known dateCreated value.
- Tries to add a 4th Form_mdvrecinto row BUT with a dateCreated that IS NOT in any rows (due to the no row being extracted the attempt to insert a row which would result in a FK conflict is not attempted).
:-
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbhelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbhelper = DatabaseHelper.getInstance(this);
long f1 = dbhelper.insertForm(10L,"2021-02-10 10:21:13"); /* id will be 10 */
long f2 = dbhelper.insertForm("2021-02-10 11:13:55"); /* id will probably be 11 */
long f3 = dbhelper.insertForm(1000l,"2021-02-10 09:00:00");
long fm1 = dbhelper.insertFormMVDRecinto(f1,"Blah001");
long fm2 = dbhelper.insertFormMVDRecinto(100l,f1,"Blah002");
/* WARNING if more than 1 form with 2021-02-10 09:00:00 then could be related to any 1 */
long fm3 = dbhelper.insertFormMVDRecinto(null,"2021-02-10 09:00:00","Blah003");
/* WILL NOT INSERT as no such dateCreated value in the Form table*/
/* does not attempt to insert into the Form_mdvrecinto because the row is not found/extracted */
long fm4 = dbhelper.insertFormMVDRecinto(null,"NOT A DATE THAT WOULD EXIST","Blah004");
}
}