Java & SQL - Can't create duplicate values in my primary and foreign key

Question

Go easy on me, middle school teacher taking a CS class. I've got a Java program that asks for user name, height, weight, does some calculations and gives results to the user. I now need to store this data in a database. I can get the data to store until I start using primary and foreign keys.

Here is the error I can't figure out: Error: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL180429151131780' defined on 'USERPROFILE'.

Here is my table:

drop table stayfitapp.userdata;
drop table stayfitapp.userprofile;
drop schema stayfitapp restrict;

create schema stayfitapp;

create table stayfitapp.userprofile
(
    profileName varchar(255) not null primary key, 
    profileGender varchar(255) not null
);

create table stayfitapp.userdata
( 
    profileAge double not null,
    profileWeight double not null,
    profileHeight double not null,
    profileWaistCircumference double not null,
    profileHipCircumference double not null,
    profileName varchar(255),
    foreign key (profileName) references stayfitapp.userprofile(profileName)
);

Here is the section of the "app" that writes to the table...

public void save(){
    try {
        String query = "insert into stayfitapp.userprofile" + "(profileName, profileGender)" + "values" + "(?,?)"; 

        String query2 = "insert into stayfitapp.userdata" + "(profileAge, profileWeight, profileHeight, profileWaistCircumference, profileHipCircumference)" + "values" + "(?,?,?,?,?)";


    Connection myConnection = DriverManager.getConnection("jdbc:derby://localhost:1527/stayfitDB2", "username", "password");

    Statement myStatement = myConnection.createStatement();
    //Statement myStatement2 = myConnection.createStatement();

    PreparedStatement prepared = myConnection.prepareStatement(query);
    prepared.setString(1, profileName);
    prepared.setString(2, profileGender);

    PreparedStatement prepared2 = myConnection.prepareStatement(query2);
    prepared2.setDouble(1, profileAge);
    prepared2.setDouble(2, profileWeight);
    prepared2.setDouble(3, profileHeight);
    prepared2.setDouble(4, profileWaistCircumference);
    prepared2.setDouble(5, profileHipCircumference);

    int rowsAffected = prepared.executeUpdate(); 
    int rowsAffected2 = prepared2.executeUpdate();

    if(rowsAffected==0)
    {
        System.out.println("Warning: User data did not save!");
    }
    else
    {
        System.out.println("User info saved!");
    }
}
catch(SQLException e)
{
    System.out.println("Error: "+e.toString());
}

Table userprofile, column: profileName varchar(255) not null primary key, - the primary key means, that values in this column must be unique, you cannot have two or more records with the same value in this column. You are trying to insert for example John, but another John is already in the table, and the database complains. — krokodilko
– krokodilko, Commented Apr 29, 2018 at 20:44
Thank you. The idea is the user comes back to use the program and their new data (same username) is stored. — BryanH
– BryanH, Commented Apr 29, 2018 at 20:53

pygator · Accepted Answer · 2018-04-29 20:56:39Z

1

Your save() method will attempt to add the user to the stayfitapp.userprofile table. This table has a field called profileName. profileName is the "primary key" so no duplicate values are allowed. The error that you are getting is saying that you cannot add(insert) the record to the table because the table already has a record with the same name. Does your program work okay if you use a different name each time?

You will need to add some logic to your program to deal with the scenario where the profileName already exists in the table. This will probably involve deleting or updating the existing record.

answered Apr 29, 2018 at 20:56

pygator

1213 bronze badges

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

8 Comments

BryanH Over a year ago

Yes, different profile names update the table just fine. I need to have multiple records for each user because the program later goes on to average the current user's weights, BMI, etc.

pygator Over a year ago

If you want multiple records for each user then you probably don't want to make the profileName the primary key.

BryanH Over a year ago

Could I just skip updating the primary key table if this user has used the program in the past? Or is that poor form?

pygator Over a year ago

It would help to know more about the class assignment. My guess is that you will be working towards having a UserProfile(id,name) table and a UserDetail table(id,weight, height etc). The two tables would be linked with primary and foreign keys.

BryanH Over a year ago

You basically guessed it... 1) We created a Java application that collected user name, age, height, weight, hip, waist. 2) We did calculations on that data 3) We stored that data in ArrayLists and calculated average, maximums, and minimums 4) We created tables (as shown above) and stored data in those tables via SQL 5) We're now integrating the Java application and tables. The tables will store the data instead of the ArrayList. We'll perform the average, min, and max calculations on the data in the tables.

|

Dan Bracuk · Accepted Answer · 2018-04-29 22:18:11Z

0

This is the problem.

insert into stayfitapp.userprofile" 
+ "(profileName, profileGender)" + "values" , etc

You have nothing to check to see if a record already exists. Something like this would work better.

insert into stayfitapp.userprofile
profileName, profileGender
select distinct ?, ?
from someSmallTable
where not exists (
select 1
from stayfitapp.userprofile
where profileName = ?
)

The someSmallTable bit depends on your database engine, which you didn't specify.

answered Apr 29, 2018 at 22:18

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

2 Comments

BryanH Over a year ago

I'm not sure what exactly "database engine" means. Maybe jdbc:derby

Dan Bracuk Over a year ago

Database Engine is another word for software or Relational Data Base Management System (RDBMS). Examples include Sql Server, Oracle, Sybase, and MySQL.

BryanH · Accepted Answer · 2018-04-30 03:46:06Z

0

I ended up writing a method to check if the username was already in the profile table. If the username was a duplicate I only wrote to the data table. If the username was new I wrote to both tables.

Thank you for your help! I'm sure there was a more efficient method (figuratively and literally) but I'm on to my final project and nearly surviving an actual CS class.

answered Apr 30, 2018 at 3:46

BryanH

112 bronze badges

Collectives™ on Stack Overflow

Java & SQL - Can't create duplicate values in my primary and foreign key

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related