Disallow Duplicates when adding to SQLite Database

Question

I currently have a student grade/class input program which accepts the following inputs: Student ID, First Name, Last Name, Class ID, Class Name, Grade Point, and Letter Grade.

For obvious reasons I would like to limit the user from entering Duplicate records for the same student/course (student id, class id) pair, as well as duplicate records for the same student id and first name/last name. (Two students should not be able to fill the same ID.

Currently I have a very basic method to add this data, what is the best method to implement my intentions?:

 db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS CUSTOMER_TABLE(studentid VARCHAR, fname VARCHAR, lname VARCHAR, classid VARCHAR, classname VARCHAR, pointgrade INTEGER, lettergrade VARCHAR);");


    add.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            if(fname.getText().toString().trim().length()==0||
                    lname.getText().toString().trim().length()==0 || studentid.getText().toString().trim().length()==0)
            {
                showMessage("Error", "Please enter First & Last Name and Student ID");
                return;
            }
            db.execSQL("INSERT INTO CUSTOMER_TABLE VALUES('"+studentid.getText()+"','"+fname.getText()+"','"+lname.getText()+"','"+classid.getText()+"','"+classname.getText()+
                    "','"+pointgrade.getText()+"','"+lettergrade.getText()+"');");
            showMessage("Success", "Student Record added successfully");
            clearText();
        }
    });

Answer 1

You can use method insertWithOnConflict with CONFLICT_IGNORE flag, if you don't want to replace this rows. If there is no same raw, method returns -1, so you can handle it

EDITED:

At first, you need to create UNIQUE rows (in your case - class id and student id):

db.execSQL("CREATE TABLE YOURDB (" 
            + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
            + "STUDENTID TEXT UNIQUE, " 
            + "CLASSID TEXT UNIQUE);"); 

then create variable for checkking result of method

int k = 0;

try to insert values and get result into our k

try {
            SQLiteDatabase db = dbHelper.getWritableDatabase();
            k = db.insertWithOnConflict("YOURDB",null, contentValues, SQLiteDatabase.CONFLICT_IGNORE);

            db.close();
        }

and, finally check your variable (I was made all in AsyncTask, so method for check is located in onPostExecute() method)

@Override
    protected void onPostExecute(Void aVoid) {
        super.onPostExecute(aVoid);
        if (k==-1){
            Toast.makeText(getApplicationContext(), "Same raws", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(getApplicationContext(), "not same raws", Toast.LENGTH_SHORT).show();
        }
    }

if there are coincidence of values, DataBase will not be updated and your k get -1 value, so, you can make Toast or something else to handle it

EDIT 2:

about ContentValue initialize:

at first, you should get values, for example, you get them from editText:

String studentIdUpdate = studentEditText.getText().toString();
String classIdUpdate = classEditText.getText().toString();

and then create ContentValues variable and put values into it

ContentValues contentValues = new ContentValues();
contentValues.put("STUDENTID", studentIdUpdate);
contentValues.put("CLASSID", classIdUpdate);

Answer 2

When you use a simple flat file containing all that information, it is easy to get things out of sync. That is, same student ID associated with multiple names, or same class ID associated with multiple class names. And extra work is required to keep things in sync if a student name or class name needs to be changed. Not to mention the need to minimize duplicate records. The first step to sorting out this mess is to redesign your database. For the data you mention here, I would use three tables:

Students
ID            Name
------------  ---------------------------------
1             Henry
2             Molly
3             George

Classes
ID            Name
------------  --------------------------------
1             Ohio History
2             US History
3             World History

Grades
StudentID     ClassID       Grade         LetterGrade
------------  ------------  ------------  ------------
1             1             98            A
2             3             85            B
3             2             77            C
1             2             85            B
3             3             92            A

Set the primary key on Students and Classes to the ID field, and for Grades to a composite of (StudentID, ClassID). This will prevent a given student from having multiple grades for the same class, and will also prevent multiple students from having the same id. Same for classes.

Now your user interface can let the user choose a student, choose a class, then assign a grade. The letter grade can be calculated, or keyed.

---

Here is how I would define the tables:

create table if not exists students (
  id          integer primary key autoincrement,
  last_name   varchar,
  first_name  varchar);

create table if not exists classes (
  id          integer primary key autoincrement,
  name        varchar);

create table if not exists grades (
  student_id    integer not null,
  class_id      integer not null,
  point_grade   integer,
  letter_grade  varchar,
  primary key (student_id, class_id),
  foreign key (student_id) references students,
  foreign key (class_id) references classes)
without rowid;

The foreign key constraints prevents grades from being entered for non-existent students or classes, and also prevents students or classes with grades from being deleted. There are other clauses to allow you to delete all grades for a student or class if a student or class is deleted.

The relationship between the students and classes is called a many-to-many relationship. That means that many students can be assigned to a single class, and many classes can be assigned to a single student. Not that the only keys that are auto increment are the student and class ID's. The ID's in the grades file reference the associated student and class rows. In the above data example, Henry has grades for two classes (Ohio History (98) and US History (85)), Molly has grades for only one class (World History (85)), and George has grades for two classes (US History (77) and World History (92)).

You can create a single view that combines the students classes and grades like this:

create view if not exists student_view (
  last_name, first_name, class_name, point_grade, letter_grade)
as (
  select last_name, first_name, name, point_grade, letter_grade
    from students
      join grades on grades.student_id = students.id
      join classes on classes.id = grades.class_id;