Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using phpmyadmin to create a table with two Unique fields

Posted on 2014-07-27
20
Medium Priority
?
894 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 84

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 111

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 84

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 111

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 84

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 111

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 84

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 111

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 111

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 111

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 111

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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

721 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