在Android的SQLite的外键/(SQLite foreign key in android/

我工作在Android 2.2

我已经创建了两个台和第二台有外键作为第一表的主键，但在插入时的外键列留空白..这里的表..

public static final String KEY_Username = "username";
public static final String KEY_Password = "password";
public static final String KEY_SITE = "site";

private static final String DATABASE_NAME = "test";
private static final int DATABASE_VERSION = 1;

private static final String SERVICE_OREDER = "Service_Order";
private static final String ERROR_CODE = "Error_Code";
private static final String SERVICE_OPERATION = "Service_Operation";
private static final String COMPONENT = "Component";
private static final String TECHNICIAN = "Technician";



//------------------------Service Order Table---------------------
private static final String KEY_SERVICE_ORDER_RECORD_NO="service_order_record_no";
private static final String KEY_OREDR_TYPE="order_type";
private static final String KEY_PRIORITY="priority";
private static final String KEY_PLANT="plant";
private static final String KEY_FUNCTIONAL_LOCATION="functional_location";
private static final String KEY_EQUIPMENT_DESC="equipment_description";
private static final String KEY_EQUIPMENT_NO="equipment_no";
private static final String KEY_CUSTOMER_ID="customer_id";
private static final String KEY_CUSTOMER_NAME="customer_name";
private static final String KEY_WIND_SPEED="wind_speed";
private static final String KEY_TOTAL_PRODUCTION="total_production";
private static final String KEY_NCR="NCR";
private static final String KEY_TOTAL_GRID_HR="total_grid_hours";
private static final String KEY_TOTAL_OPERATION_HR="total_operation_hours";
private static final String KEY_SERVICE_VISIT="service_visit_p_year";
private static final String KEY_ORDER_DESC="order_description";
private static final String KEY_ERRORCODE_START_DT="error_code_start_date_and_time";
private static final String KEY_MALFUNCTION_START_DT="malfunction_start_date_and_time";
private static final String KEY_MALFUNCTION_END_DT="malfunction_end_date_and_time";

private static final String SERVICE_ORDER_TABLE_CREATE = "CREATE TABLE "+SERVICE_OREDER+" ("+KEY_SERVICE_ORDER_RECORD_NO+" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
        ""+KEY_OREDR_TYPE+" TEXT NOT NULL , "+KEY_PRIORITY+" TEXT, "+KEY_PLANT+" TEXT, "+KEY_FUNCTIONAL_LOCATION+" TEXT, "+
        ""+KEY_EQUIPMENT_DESC+" TEXT,"+KEY_EQUIPMENT_NO+" TEXT, "+KEY_CUSTOMER_ID+" TEXT, "+KEY_CUSTOMER_NAME+" TEXT,"+
        ""+KEY_WIND_SPEED+" TEXT, "+KEY_TOTAL_PRODUCTION+" TEXT, "+KEY_NCR+" TEXT,"+KEY_TOTAL_GRID_HR+" TEXT,"+
        ""+KEY_TOTAL_OPERATION_HR+" TEXT, "+KEY_SERVICE_VISIT+" TEXT, "+KEY_ORDER_DESC+" TEXT, "+
        ""+KEY_ERRORCODE_START_DT+" TEXT, "+KEY_MALFUNCTION_START_DT+" TEXT, "+KEY_MALFUNCTION_END_DT+" TEXT)"; 



//------------------------Error Code Table---------------------
private static final String KEY_ERROR_CODE_REC_NO ="error_code_record_no";
private static final String KEY_CODE_GROUP ="code_group";
private static final String KEY_CODE ="code";
private static final String KEY_SERVICE_ORDER_REC_NO="service_order_record_no";

private static final String ERROR_CODE_TABLE_CREATE = "CREATE TABLE "+ERROR_CODE+" ("+KEY_ERROR_CODE_REC_NO+" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,"+ 
        ""+KEY_CODE_GROUP+" TEXT,"+KEY_CODE+" TEXT, "+KEY_SERVICE_ORDER_REC_NO+" INTEGER,"+
        "FOREIGN KEY("+KEY_SERVICE_ORDER_REC_NO+") REFERENCES "+SERVICE_OREDER+"("+KEY_SERVICE_ORDER_RECORD_NO+") ON DELETE CASCADE )";


//-----------------------Service Operation Table-----------------
private static final String KEY_SERVICE_OPE_REC_NO ="service_operation_record_no";
private static final String KEY_OPE_ACTIVITY ="operation_activity";
private static final String KEY_CONTROLLING_KEY ="controlling_key";
private static final String KEY_ACTION_TYPE ="action_type";
private static final String KEY_LONG_TEXT ="long_text";
private static final String KEY_WORK_HPM="work_hours_p_min";
private static final String KEY_SO_UNIT="unit";
private static final String KEY_NO_RESORCE ="no_of_resources";
private static final String KEY_ERROR_CODE_REC="error_code_record_no";

private static final String SERVICE_OPERATION_TABLE_CREATE ="CREATE TABLE "+SERVICE_OPERATION+" ("+KEY_SERVICE_OPE_REC_NO+" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,"+KEY_OPE_ACTIVITY+" TEXT, "+KEY_CONTROLLING_KEY+" TEXT, "+KEY_ACTION_TYPE+" TEXT, "+KEY_LONG_TEXT+" TEXT ,"+KEY_WORK_HPM+" TEXT, "+KEY_SO_UNIT+" TEXT , "+KEY_NO_RESORCE+" TEXT ,"+KEY_ERROR_CODE_REC+" INTEGER, FOREIGN KEY("+KEY_ERROR_CODE_REC+") REFERENCES "+ERROR_CODE+"("+KEY_ERROR_CODE_REC_NO+") ON DELETE CASCADE )";


// ------------------------Component Table----------------------
private static final String KEY_COMP_REC_NO = "component_record_no";
private static final String KEY_COMP_NO= "component_no";
private static final String KEY_COMP_DESC = "component_description";
private static final String KEY_COMP_ID  = "component_id";
private static final String KEY_REQ_QTY = "required_quantity";
private static final String KEY_UOM = "UoM";
private static final String KEY_ITEM_CAT = "item_category";
private static final String Key_OPE_ACT = "operation_activity";
private static final String KEY_STOR_LOC ="storage_location";
private static final String KEY_VAL_TYPE ="valuation_type";
private static final String KEY_SER_OPR_REC_NO = "service_operation_record_no";
private static final String KEY_SER_NO_ARRY ="serial_nos_array";

private static final String COMP_TABLE_CREATE = "create table "+COMPONENT+" ( "+KEY_COMP_REC_NO+" INTEGER primary key AUTOINCREMENT  not null,"+
                                                ""+KEY_COMP_NO+" TEXT, "+KEY_COMP_DESC+" TEXT, "+KEY_COMP_ID+"  TEXT, "+KEY_REQ_QTY+" TEXT,"+
                                                ""+KEY_UOM+" TEXT, "+KEY_ITEM_CAT+" TEXT , "+Key_OPE_ACT+" TEXT ,"+KEY_STOR_LOC+" TEXT,"+
                                                ""+KEY_VAL_TYPE+" TEXT ,"+KEY_SER_OPR_REC_NO+" INTEGER,"+KEY_SER_NO_ARRY+" TEXT,"+ 
                                                "FOREIGN KEY("+KEY_SER_OPR_REC_NO+") REFERENCES "+SERVICE_OPERATION+"("+KEY_SERVICE_OPE_REC_NO+") ON DELETE CASCADE)";

//------------------------Technician Table---------------------
private static final String KEY_TECH_REC_NO ="technician_record_no";
private static final String KEY_TECH ="technician";
private static final String KEY_WORK_HM ="work_hours_p_min";
private static final String KEY_UNIT ="unit";
private static final String KEY_DATETIME="date_and_time";
private static final String KEY_SERVICE_OP_REC_NO="service_operation_record_no";

private static final String TECH_TABLE_CREATE = "CREATE TABLE "+TECHNICIAN+" ("+KEY_TECH_REC_NO+" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+
                                                ""+KEY_TECH+" TEXT,"+KEY_WORK_HM+" TEXT, "+KEY_UNIT+" TEXT, "+KEY_DATETIME+" TEXT ,"+
                                                ""+KEY_SERVICE_OP_REC_NO+" INTEGER,FOREIGN KEY("+KEY_SERVICE_OP_REC_NO+") REFERENCES "+SERVICE_OPERATION+"("+KEY_SERVICE_OPE_REC_NO+") ON DELETE CASCADE )";


public SuzlonDBHelper(Context context, String name, CursorFactory factory,int version) {
    super(context, name, factory, version);
    // TODO Auto-generated constructor stub
}

public SuzlonDBHelper(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(SERVICE_ORDER_TABLE_CREATE);
    db.execSQL(ERROR_CODE_TABLE_CREATE);
    db.execSQL(SERVICE_OPERATION_TABLE_CREATE);
    db.execSQL(COMP_TABLE_CREATE);
    db.execSQL(TECH_TABLE_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

}


//------------------------------Insert into Service Order--------------------

public long insertServiceOrder(ServiceOrder serviceOrder){
    SQLiteDatabase db = this.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys = ON;");
        ContentValues values = new ContentValues();

        values.put(KEY_OREDR_TYPE, serviceOrder.getOrderType());
        values.put(KEY_PRIORITY, serviceOrder.getPriority());
        values.put(KEY_PLANT, serviceOrder.getPlant());
        values.put(KEY_FUNCTIONAL_LOCATION, serviceOrder.getFunctionalLocation());
        values.put(KEY_EQUIPMENT_DESC, serviceOrder.getEquipmentDescription());
        values.put(KEY_EQUIPMENT_NO, serviceOrder.getEquipmentNo());
        values.put(KEY_CUSTOMER_ID, serviceOrder.getCustomerID());
        values.put(KEY_CUSTOMER_NAME, serviceOrder.getCustomerName());
        values.put(KEY_WIND_SPEED, serviceOrder.getWindSpeed());
        values.put(KEY_TOTAL_PRODUCTION, serviceOrder.getTotalProduction());
        values.put(KEY_NCR, serviceOrder.getNCR());
        values.put(KEY_TOTAL_GRID_HR, serviceOrder.getTotalGridHour());
        values.put(KEY_TOTAL_OPERATION_HR, serviceOrder.getTotalOperationHours());
        values.put(KEY_SERVICE_VISIT, serviceOrder.getServiceVisit());
        values.put(KEY_ORDER_DESC, serviceOrder.getOrderDescription());
        values.put(KEY_ERRORCODE_START_DT, serviceOrder.getErrorCodeStartDT());
        values.put(KEY_MALFUNCTION_START_DT, serviceOrder.getMalfunctionStartDT());
        values.put(KEY_MALFUNCTION_END_DT, serviceOrder.getMalfunctionEndDT());

        Long row = db.insert(SERVICE_OREDER, null, values);
        db.close();
    return row;


}

//-------------------------------Insert into Error Code-------------------------

public long insertErrorCode(DefectDamageCode defectDamage){
    SQLiteDatabase db = this.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys = ON;");
        ContentValues values = new ContentValues();
        values.put(KEY_CODE_GROUP, defectDamage.getCodeGroups());
        values.put(KEY_CODE, defectDamage.getCodes());


        Long row = db.insert(ERROR_CODE, null, values);
        db.close();
    return row;
}


//--------------------------------Insert into Service Operation----------------------

public long insertServiceOperation(AddServiceOperation serviceOperation){
    SQLiteDatabase db = this.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys = ON;");
        ContentValues values = new ContentValues();
        values.put(KEY_OPE_ACTIVITY, serviceOperation.getOperationActivity());
        values.put(KEY_CONTROLLING_KEY, serviceOperation.getControllingKey());
        values.put(KEY_ACTION_TYPE, serviceOperation.getActivityType());
        values.put(KEY_LONG_TEXT, serviceOperation.getLongText());
        values.put(KEY_WORK_HPM, serviceOperation.getWorkHM());
        values.put(KEY_SO_UNIT, serviceOperation.getUnit());
        values.put(KEY_NO_RESORCE, serviceOperation.getNoOfResources());


        Long row = db.insert(SERVICE_OPERATION, null, values);
        db.close();
    return row;
}


//---------------------------Insert into Component------------------------------

public long insertComponent(AddComponent addComponent){
    SQLiteDatabase db = this.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys = ON;");
        ContentValues values = new ContentValues();
        values.put(KEY_COMP_NO, addComponent.getComponentNo());
        values.put(KEY_COMP_DESC, addComponent.getComponentDescription());
        values.put(KEY_COMP_ID, addComponent.getCommponentID());
        values.put(KEY_REQ_QTY, addComponent.getRequiredQty());
        values.put(KEY_UOM, addComponent.getUOM());
        values.put(KEY_ITEM_CAT, addComponent.getItemCategory());
        values.put(Key_OPE_ACT, addComponent.getOperationActivityComponent());
        values.put(KEY_STOR_LOC, addComponent.getStorageLocation());
        values.put(KEY_VAL_TYPE, addComponent.getValuationType());
        //values.put(KEY_SER_NO_ARRY, );


        Long row = db.insert(COMPONENT, null, values);
        db.close();
    return row;
}


//-------------------------------Insert into Technician-----------------------------
public long insertTechnician(AddTechnician addTechnician){
    SQLiteDatabase db = this.getWritableDatabase();
    //db.execSQL("PRAGMA foreign_keys = ON;");

        ContentValues values = new ContentValues();
        values.put(KEY_TECH, addTechnician.getTechnician());
        values.put(KEY_WORK_HM, addTechnician.getWorkHMTechnician());
        values.put(KEY_UNIT, addTechnician.getUnitTechnician());
        values.put(KEY_DATETIME, addTechnician.getDateTimeTechnician());

        Long row = db.insert(TECHNICIAN, null, values);
        db.close();
    return row;
}

}

我是否需要手动提供它或它会被分贝管理.. ???

PPLZ帮助

谢谢..

Answer 1:

为了实施ForeignKeys，你需要执行下面的SQL语句每次打开数据库连接：

db.execSQL("PRAGMA foreign_keys = ON;");

之后，您就可以使用db对象的常用方式。

文章来源: SQLite foreign key in android/