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();
        }
    });

FIL201121 · Accepted Answer · 2018-05-30 18:39:26Z

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);

edited May 30, 2018 at 18:39

answered May 30, 2018 at 14:43

FIL201121

1241 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tanner Tattini Over a year ago

Can you please give an example?

Tanner Tattini Over a year ago

I don't think that I am entering the contentValues correctly, can you assist me with that:

Tanner Tattini Over a year ago

k = db.insertWithOnConflict("STUDENT_TABLE",null, studentid.getText() + fname.getText() + lname.getText(), SQLiteDatabase.CONFLICT_IGNORE);

Tanner Tattini Over a year ago

Now this I can work with thank you! Is there a way to join these separate tables back together after the add section?

FIL201121 Over a year ago

If I understood correctly, you create Table in 'db.execSQL()' method with columns "STUDENTID" and "CLASSID", or whatever you did. So, STUDENTID and CLASSID are columns of the table with name YOURDB (this is how it is in my example). So, they are in the same table.

jmarkmurphy · Accepted Answer · 2018-05-30 15:29:45Z

1

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;

edited May 30, 2018 at 15:29

answered May 30, 2018 at 14:35

jmarkmurphy

11.6k37 silver badges62 bronze badges

4 Comments

Tanner Tattini Over a year ago

To create the three tables, would I just use "db.execSQL("CREATE TABLE IF NOT EXISTS TABLE_#" Three different times, and then just populate the corresponding table?

Tanner Tattini Over a year ago

Such as: db.execSQL("CREATE TABLE IF NOT EXISTS CUSTOMER_TABLE(studentid VARCHAR, fname VARCHAR, lname VARCHAR);"); db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid VARCHAR, classname VARCHAR);"); db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE(pointgrade INTEGER, lettergrade VARCHAR);");

Tanner Tattini Over a year ago

One student can be enrolled in multiple classes however, therefore auto increment wouldn't work right?

jmarkmurphy Over a year ago

You only have one student record per student, and one class record per class. So auto increment will work properly. The missing piece is that there is one grades record per student per class, this is the student/class cross-reference.

Collectives™ on Stack Overflow

Disallow Duplicates when adding to SQLite Database

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related