Solved

Using phpmyadmin to create a table with two Unique fields

Posted on 2014-07-27
20
741 Views
Last Modified: 2014-08-11
I have a table already created, and one of the fields "member_id" was set to unique when I first created the table.

Then later I went back to also make another field "member_username" unique also,  I get the following error:
#1062 - Duplicate entry '' for key 'member_username'

Here is the code the phpmyadmin used, but gave above error message:
ALTER TABLE  `members` ADD UNIQUE (
`member_username`
);
0
Comment
Question by:keith1001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
20 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40222958
It appears that UNIQUE can only be used for one column in a table and that must be the primary key.  http://www.w3schools.com/sql/sql_unique.asp
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40222987
@Dave: I don't read it that way.  
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
My guess is that this is a data-dependent error.  Please post the data that is in the member_username column.  When you SELECT this column to create the post, use ORDER BY member_username so we can see them in order, thanks.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40223025
In my MySQL manual, the only use of the UNIQUE keyword is to create a UNIQUE index, not a unique column.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40223081
Agreed, but if there is only one column under the UNIQUE index, then all the values in that column must be unique.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40223113
Maybe.  Re-reading the question, is there already a key/index on that column which is causing the error message?

#1062 - Duplicate entry '' for key 'member_username'

Or maybe there are are duplicates in the existing values??
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40223128
Dave, I'm thinking the latter.  When the author posts the sorted list of the column values, I can copy those into a PHP array and apply array_not_unique() to prove or disprove the hypothesis.

<?php // demo/array_not_unique.php
error_reporting(E_ALL);
echo "<pre>";

// A FUNCTION TO FIND REPLICATED VALUES IN AN ARRAY
function array_not_unique($raw)
{
    // MAN PAGE: http://php.net/manual/en/function.array-count-values.php
    $new = array_count_values($raw);
    foreach ($new as $key => $val)
    {
        if ($val < 2) unset($new[$key]);
    }
    return $new;
}


// SOME TEST DATA
$raw_array   = array();
$raw_array[] = 'abc@xyz.com';
$raw_array[] = 'def@xyz.com';
$raw_array[] = 'ghi@xyz.com';
$raw_array[] = 'jkl@xyz.com';
$raw_array[] = 'mno@xyz.com';
$raw_array[] = 'pqr@xyz.com';
$raw_array[] = 'stu@xyz.com';

// SOME DUPLICATES
$raw_array[] = 'abc@xyz.com';
$raw_array[] = 'jkl@xyz.com';
$raw_array[] = 'abc@xyz.com';
$raw_array[] = 'def@xyz.com';


// SHOW THE FUNCTION AT WORK
$common = array_not_unique($raw_array);
foreach ($common as $x => $n)
{
    echo PHP_EOL . "THE VALUE $x APPEARED $n TIMES";
}

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40223155
That appears to be the problem.  In phpMyAdmin, I was able to create a UNIQUE index on a column with no dupes but when I tried to do it on another one that had duplicates, I got the error message "#1062 - Duplicate entry for key ...".
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40223162
Yeah, #1062 is pretty familiar!
0
 

Author Comment

by:keith1001
ID: 40223262
SELECT *
FROM  `members`
ORDER BY  `member_username`
LIMIT 0 , 30

id      member_id      member_firstname      member_lastname      member_username      member_password      member_email      member_access_level
3      3      Larry      Last3      Larry      LarryPW      larry@mail.com      4
1      1      Keith      Last1      username1      admin      admin@mail.com      10
2      2      Doug      Last2      username2      admin2      amin2@email.com      5


Let me know if this will work?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40223345
It will not work reliably with a LIMIT clause.  The UNIQUE characteristic covers every single row, not a limited subset of rows.  We need to see all of the rows, every single one.
0
 

Author Comment

by:keith1001
ID: 40225112
$q = "SELECT * FROM members GROUP BY member_username";
	$result = $mysqli->query($q);
	echo '<pre>';
	var_dump($result);
	echo '</pre>';
	while ($row = $result->fetch_object()){
		echo ($row->id) . ' - ';
		echo ($row->member_id). ' - ';
		echo ($row->member_firstname). ' - ';
		echo ($row->member_lastname). ' - ';
		echo ($row->member_username). ' - ';
		echo ($row->member_password). ' - ';
		echo ($row->member_email). ' - ';
		echo ($row->member_access_level);
		echo '<br>';
	}

Open in new window


Result:
object(mysqli_result)[3]
  public 'current_field' => null
  public 'field_count' => null
  public 'lengths' => null
  public 'num_rows' => null
  public 'type' => null
3 - 3 - Larry - Last3 - Larry - LarryPW - larry@mail.com - 4
1 - 1 - Keith - Last1 - username1 - admin - admin@mail.com - 10
2 - 2 - Doug - Last2 - username2 - admin2 - amin2@email.com - 5

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40225575
OK, what is the question at this point?  Are those all of the rows in the table?  Please post the CREATE TABLE statement, thanks.
0
 

Author Comment

by:keith1001
ID: 40225654
I just did the create table statement in phpmyadmin.

Right now the member_id column is set to unique.  I also want to set the member_username to unique also.  But while I'm using phpmyadmin it gives me the error from the 1st post.

Those are all the fields right now, I can add more if necessary?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40225728
To get the CREATE TABLE statement, you use the SHOW CREATE TABLE query.  If you will post that statement it will help me help you.

Databases have "rows" and "columns" but the term "fields" is a little ambiguous.

What I would like to do is use the CREATE TABLE statement and some INSERT statements to load data into the table.  Then I can use ALTER TABLE to show you how to add the UNIQUE index on a second column.  I will do this in PHP, but the MySQL queries will be obvious in the script I post.
0
 

Author Comment

by:keith1001
ID: 40225803
it shows two unique keys but I was clicking on member_password unique button.

'CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `member_firstname` varchar(25) NOT NULL,
  `member_lastname` varchar(25) NOT NULL,
  `member_username` varchar(25) NOT NULL,
  `member_password` varchar(25) NOT NULL,
  `member_email` varchar(30) NOT NULL,
  `member_access_level` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id` (`member_id`),
  UNIQUE KEY `member_id_2` (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1'

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40226416
Let's focus in on this:

  UNIQUE KEY `member_id` (`member_id`),
  UNIQUE KEY `member_id_2` (`member_id`)

Both of the UNIQUE KEY fields cover the same column!  I've never seen anything like this.  What columns did you really want to make UNIQUE?  Perhaps we can rebuild the table or remove one of the keys?
0
 

Author Comment

by:keith1001
ID: 40235541
I have never seen this either and have done multiple unique keys in the past on different columns.  I think PHPMyadmin screwed up.

But I need `member_id` and `member_username` to be the unique columns.  Right now they are unique because that is the way I entered the data in.

Let me know what you want to try?
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40235545
I think I would try something like this:

'CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `member_firstname` varchar(25) NOT NULL,
  `member_lastname` varchar(25) NOT NULL,
  `member_username` varchar(25) NOT NULL,
  `member_password` varchar(25) NOT NULL,
  `member_email` varchar(30) NOT NULL,
  `member_access_level` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id` (`member_id`),
  UNIQUE KEY `member_username` (`member_username`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1'

Open in new window

This should create two independent UNIQUE indexes.
0
 

Author Comment

by:keith1001
ID: 40235546
Now what is strange I went back into phpmyadmin and looked around some more and just looked at the table, and thought I would give it another try, I clicked 'Inique' for the `member_username` and it worked fine with no errors.

So I tried one more column, again it worked just fine.  Now I just need the command or figure how to "un-unique" the extra I did.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 40236036
There is probably a checkbox, or you may want to recreate the table.  Glad you've got it working now.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question