Solved

Using phpmyadmin to create a table with two Unique fields

Posted on 2014-07-27
20
597 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
  • 10
  • 6
  • 4
20 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
@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 82

Expert Comment

by:Dave Baldwin
Comment Utility
In my MySQL manual, the only use of the UNIQUE keyword is to create a UNIQUE index, not a unique column.
0
 
LVL 108

Expert Comment

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

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Yeah, #1062 is pretty familiar!
0
 

Author Comment

by:keith1001
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:keith1001
Comment Utility
$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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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 108

Assisted Solution

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
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 …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now