Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 943
  • Last Modified:

Using phpmyadmin to create a table with two Unique fields

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
keith1001
Asked:
keith1001
  • 10
  • 6
  • 4
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
@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
 
Dave BaldwinFixer of ProblemsCommented:
In my MySQL manual, the only use of the UNIQUE keyword is to create a UNIQUE index, not a unique column.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Ray PaseurCommented:
Agreed, but if there is only one column under the UNIQUE index, then all the values in that column must be unique.
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
Yeah, #1062 is pretty familiar!
0
 
keith1001Author Commented:
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
 
Ray PaseurCommented:
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
 
keith1001Author Commented:
$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
 
Ray PaseurCommented:
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
 
keith1001Author Commented:
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
 
Ray PaseurCommented:
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
 
keith1001Author Commented:
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
 
Ray PaseurCommented:
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
 
keith1001Author Commented:
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
 
Ray PaseurCommented:
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
 
keith1001Author Commented:
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
 
Ray PaseurCommented:
There is probably a checkbox, or you may want to recreate the table.  Glad you've got it working now.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 10
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now