0

I have the tables medical_user and a table operation_issue:

CREATE TABLE medical_user(_id INTEGER,medical_id TEXT,creation_date DATE,update_date DATE,birth_date DATE,gender TEXT,bmi DOUBLE,is_deleted INTEGER DEFAULT 0 NOT NULL CHECK(is_deleted IN (0,1)) ,PRIMARY KEY (medical_id,creation_date));

CREATE TABLE operation_issue(operation_issue_name TEXT,medical_user_id TEXT,creation_date DATE,update_date DATE,PRIMARY KEY (operation_issue_name)  FOREIGN KEY(medical_user_id) REFERENCES medical_user(medical_id) ON UPDATE CASCADE);

First I insert successfully a user: User70666661 with following method:

public long insert(MedicalUser medicalUser) {
    if (medicalUser == null)
        return -1;

    try {
        ContentValues values = getUserContentValues(medicalUser);
        long ret = database.insertOrThrow(DBContracts.MedicalUserTable.TABLE_NAME, null, values);
        UtilsRG.log.info("Inserted user(" + medicalUser.getMedicalId() + ") into databse successfully");
        return ret;
    } catch (Exception e) {
        UtilsRG.error("Failed to insert medicalUser: " + medicalUser.getMedicalId() + " ErrorMessage:" + e.getLocalizedMessage());
        return -1L;
    }
}

Getting this log: Inserted user(User70666661) into databse successfully

Then I try to insert an operation_issue

android.database.sqlite.SQLiteException: foreign key mismatch - "operation_issue" referencing "medical_user" (code 1): , while compiling: INSERT INTO operation_issue VALUES('super hot issue','User70666661','2016-11-10 12:10:07.080','2016-11-10 12:10:07.081');

Update 1:

Using this getUserContentValues() method:

private ContentValues getUserContentValues(MedicalUser medicalUser) {
    ContentValues values = new ContentValues();
    if (medicalUser.getMedicalId() != null)
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_MEDICAL_ID, medicalUser.getMedicalId());

    if (medicalUser.getCreationDate() != null) {
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_CREATION_DATE, UtilsRG.dateFormat.format(medicalUser.getCreationDate()));
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_UPDATE_DATE, UtilsRG.dateFormat.format(medicalUser.getUpdateDate()));
    }

    if (medicalUser.getBirthDate() != null)
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_BIRTH_DATE, UtilsRG.dateFormat.format(medicalUser.getBirthDate()));

    if (medicalUser.getGender() != null && (!medicalUser.getGender().equals("")))
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_GENDER, medicalUser.getGender().name());

    if (medicalUser.getBmi() > 0)
        values.put(DBContracts.MedicalUserTable.COLUMN_NAME_BMI, medicalUser.getBmi());

    values.put(DBContracts.MedicalUserTable.COLUMN_MARKED_AS_DELETE, (medicalUser.isMarkedAsDeleted()) ? 1 : 0);
    return values;
}
lidox
  • 1,901
  • 3
  • 21
  • 40
  • 1
    *"operation_issue" referencing "medical_user"* ... `VALUES('super hot issue','User70666661','2016-11-10 12:10:07.080','2016-11-10 12:10:07.081')` <= where is medical_user_id? – Selvin Nov 10 '16 at 11:16
  • yeah I get the user by Id – lidox Nov 10 '16 at 11:17
  • 1
    it is hard to say without knowladge about ContentValues used for insert – Selvin Nov 10 '16 at 11:19
  • see update 1 :) thanks – lidox Nov 10 '16 at 11:23
  • 2
    ok *Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table* – Selvin Nov 10 '16 at 11:23
  • 1
    and now you have: `medical_user ... PRIMARY KEY (medical_id,creation_date)` ...`operation_issue ... FOREIGN KEY(medical_user_id) REFERENCES medical_user(medical_id)` ... and medical_id is not marked as UNIQUE nor a primary key – Selvin Nov 10 '16 at 11:24
  • so do you have suggestions how to solve this issue? I dont have an idea, how to get it work – lidox Nov 10 '16 at 11:31
  • 1
    add UNIQUE constrain to medical_user(medical_id) ... which makes creation_date in medical_user PRIMARY KEY (medical_id,creation_date) useles ... so obviously change medical_user PRIMARY KEY contain only medical_id – Selvin Nov 10 '16 at 11:33
  • commit your comment as an answer. i will accept it. You solved my problem! Thanks so much!!! :) – lidox Nov 10 '16 at 11:37

0 Answers0