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
Solved

Using phpmyadmin to create a table with two Unique fields

Posted on 2014-07-27
20
685 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 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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

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 109

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 109

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 109

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 109

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 109

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 109

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 109

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 109

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Have issues with Query MySQL 9 72
setup wamp server for first time 2 83
insert row field data graphically 4 28
Very Large data in MYSQL 7 73
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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