From: <sinisa
Date: March 21 1999 7:02pm
Subject: Primary Key trouble
List-Archive: http://lists.mysql.com/mysql/726
Message-Id: <14069.16945.681164.8503@monster.local.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Andy Harrison writes:
 > I'm working on getting Access to use my mySQL database and I've had a
 > lot of success.  But now I'm stuck on something that I just can't
 > knock.  In order for the myAccess add-in to work 100%, you have to have
 > a primary key defined.  No problem, I had it working fine.  Access could
 > enter data, no sweat.
 > 
 > After I got it working, I went and made some changes and basically
 > recreated the mySQL database I had going.  Now it's time for the primary
 > key.  I'm keying off of a char(50) field called simply Name.  There are
 > no duplicate values in this.  I combed the entire table several times
 > ensuring there were no dupes, plus had someone else comb through it as
 > well.  There are 552 rows of data and I'm 110% certain that no two Name
 > fields contain the same data.
 > 
 > Now, when I go to create the primary key on the name field, I modify the
 > Name field appropriately, click the Alter Table button, and I get:
 > 
 > Executing "alter table ip add primary key (Name(30)) " -> * ERROR *
 > Can't write, duplicate key in table 'A-280'
 > 
 > (the reason it says (Name(30)) in this example is because I was trying a
 > smaller key to see if that helped)
 > 
 > I'm not sure quite what that error message means.  I looked at row 280
 > to see if that contained a dupe and it didn't.
 > 
 > Can anyone help me out?  Thanx!
 > 
 > --
 > 
 >           .==,_
 >          .===,_`\                                Andy Harrison
 >        .====,_ ` \      .====,__
 >  ---     .==-,`~. \           `:`.__,     __
 >   ---      `~~=-.  \           /^^^      / /    __  _  _  _  _ __  __
 >     ---       `~~=. \         /         / /__  / / / \// //_// \ \/ /
 >                  `~. \       /         /____/ /_/ /_/\/ /___/  /_/\_\
 >                    ~. \____./           ...for IQs GREATER than 98...
 >                      `.=====
 >                   ___.--~~~--.__
 >         ___\.--~~~              ~~~---.._|/
 >         ~~~"                             /
 > 
 > 
 > 

Hi!

First of all, if you create index on the first N chars, you should
look for dups in the first N chars, not entire field. 

Next, if your field is not binary, upper and lower case amounts to the 
same char !

Sinisa

+---------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___        ==  mysql@stripped        |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|        Sinisa Milivojevic      |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|        mailto:sinisa@stripped |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|        Belgrade                |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____   Serbia                  |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                           |
|             /*/             \*\                   Developers Team   |
+---------------------------------------------------------------------+