MySQL database has duplicate entries despite unique constraint

Question

So mySQL database keeps accepting duplicate entries despite the unique constraint I've added. I've been trying to look for a similar question but could not find anything related. I don't know if my prepared statement is wrong or if mySQL database is set up wrong. Here is my code:

    Connection con = DB.getConnection();
    PreparedStatement ps = null, ps1 = null, ps2 = null, ps3 = null, ps4 = null;
    ResultSet rs = null;
    try {
        ps = con.prepareStatement("insert into users(user_id, username, password, email, phone_number,"
                + "name, fb_id, bio) values (DEFAULT, ?, ?, ?, ?, ?, ?, ?)");
        ps.setString(1, username);
        ps.setString(2, hashedPassword);
        ps.setString(3, email_address);
        ps.setString(4, phone_number);
        ps.setString(5, name);
        ps.setString(6, fb_id);
        ps.setString(7, bio);
        ps.execute();
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          try {
              ps.close();
              con.close();
          } catch (Exception e) {
            e.printStackTrace(); }

my table has the following columns:

user_id int(11) PRIMARY,
username varchar(20) UNIQUE,
password varchar(100),
email varchar(30) UNIQUE,
phone_number varchar(10) UNIQUE,
fb_id varchar(20) UNIQUE,
bio FULL TEXT

I've created the table using phpmyadmin and username and fb_id correctly gives me the MySQLIntegrityConstraintViolationException, but email and phone_number does not give me any errors when inserting duplicate entries. Is there any reason why this would happen? I am pretty lost as to why the unique constraint is not being enforced...

PS. First time posting!

EDIT: Here is the results of SHOW CREATE TABLE users:

CREATE TABLE users ( user_id int(11) NOT NULL AUTO_INCREMENT, username varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, password varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, email varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, phone_number varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, name varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, fb_id varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, bio text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (user_id), UNIQUE KEY username (username,email,phone_number), UNIQUE KEY fb_id (fb_id) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1

Can you post the output of SHOW CREATE TABLE users;? It will show the current defined structure of the table, not just what phpMyAdmin chooses. — Sammitch
– Sammitch, Commented May 27, 2014 at 23:27
it seems as if the unique constraint for username addresses for email and phone number as well? In other words, phone numbers and emails can be the same as long as the username is different? — airborne4
– airborne4, Commented May 27, 2014 at 23:42

Alex.Ritna · Accepted Answer · 2014-05-27 23:51:12Z

1

You currently have your UNIQUE KEY setup as a composite key.

UNIQUE KEY username (username,email,phone_number)

This means that the combination of username, email and phone number must be unique. It is not specifying each individual column can't contain duplicate values.

For that you should add a UNIQUE INDEX to those columns individually where required. For example to make the email field a UNIQUE INDEX:

ALTER TABLE users ADD UNIQUE INDEX(email);

answered May 27, 2014 at 23:51

Alex.Ritna

1,9842 gold badges17 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

MySQL database has duplicate entries despite unique constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related