How to delete a particluar column in sqlite with it's referencing columns

Question

In my app,I have to add expenses and budget for particular categories.One category can have one or many expenses and one category can have only one budget amount.

Now the problem I'm facing is,If I delete a specific category from the table,the expenses and budget related to that category are still remain in database.

If there is no category then no need of expenses and budget related to that category also,I took category as foriegn key to the budget(TABLE2) and expenses (TABLE3)as well.I couldn't find the real problem,

DBhelper class.

public class DBhelper extends SQLiteOpenHelper {

    static final String DATABASE = "wedding9.db";
    static final int VERSION = 9;
    static final String TABLE1 = "Category";
    static final String TABLE2 = "Budget";
    static final String TABLE3 = "Expenses";

    static final String C_ID = "_id";
    static final String Name = "name";
    static final String B_ID = "_id";
    static final String Description = "description";
    static final String Amount = "amount";

    public static final String ID1 = "_id";
    public static final String DATE_T1 = "date1";
    public static final String CATEGORY = "category";
    public static final String DETAIL = "detail";
    public static final String AMOUNT1 = "amount1";
    public static final String STATUS = "status";
    public static final String EX_YEAR = "exyear";
    public static final String EX_MONTH = "exmonth";

    public DBhelper(Context context) {
        super(context, DATABASE, null, VERSION);
    }

    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE " + TABLE1 + "(" + C_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT," + Name + " text unique not null)");

        db.execSQL("CREATE TABLE " + TABLE2 + "(" + B_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT," + Description + " text,"
                + Amount + " text, FOREIGN KEY (" + Description + ") REFERENCES " + TABLE1 + "(" + Name + "));");

        db.execSQL("CREATE TABLE " + TABLE3 + " ( "
                + ID1 + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + DATE_T1 + " text, "
                + CATEGORY + " text, "
                + DETAIL + " text, "
                + STATUS + " text, "
                + EX_YEAR + " text, "
                + EX_MONTH + " text, "
                + AMOUNT1 + " text, FOREIGN KEY (" + CATEGORY + ") REFERENCES " + TABLE1 + "(" + Name + "));");


    }

this is my function to delete Category from the database.

TextView deleteBtn = (TextView) view.findViewById(R.id.delete_btn);

        deleteBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //do something
                if (context instanceof Budget_activity) {
                    new AlertDialog.Builder((Budget_activity) context)
                            .setTitle("Delete Category")
                            .setMessage("Are you sure you want to Delete this Category?")
                            .setPositiveButton(android.R.string.yes, new DialogInterface.OnClickListener() {
                                public void onClick(DialogInterface dialog, int which) {
                                    SQLiteDatabase db = new DBhelper(context.getApplicationContext()).getWritableDatabase();
                                    db.delete(DBhelper.TABLE1, DBhelper.C_ID + "=?", new String[]{Integer.toString(list.get(position).getId())});
                                    db.close();
                                    list.remove(position);
                                    notifyDataSetChanged();

                                }
                            })
                            .setNegativeButton(android.R.string.no, new DialogInterface.OnClickListener() {
                                public void onClick(DialogInterface dialog, int which) {
                                    // do nothing
                                }
                            })
                            .setIcon(android.R.drawable.ic_dialog_alert)
                            .show();

                }
            }
        });
        return view;
    }
}

score 1 · Answer 1 · answered Oct 29 '15 at 08:56

1

ALTER TABLE table_name DROP COLUMN column_name;

answered Oct 29 '15 at 08:56

5511002233

503
8
19

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

If i right understand, you have to make foreign keys with cascade option on delete. Look here

edited May 23 '17 at 11:44

Community

1
1

answered Oct 29 '15 at 08:52

arthas

104
1
11

I refered it,So accordint to that answer ,they gave in that link,do I need to add this line ` mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE` inside my TABLE2 and TABLE3 or do I need this in my delete method of category,I didn't get it. – acer Oct 29 '15 at 09:15

How to delete a particluar column in sqlite with it's referencing columns

2 Answers2