Re: Unique index/primary key case-insensitive?

[Arjen Lentz]

Hi Andreas,

On Sat, 2002-02-16 at 08:52, Andreas Frøsting wrote:
> Why does MySQL have this strange behavoir, treating unique indexes
> case-insensitive?
> Can anyone explain MySQL's behaviour and how I do a workaround making
> unique indexes and primary keys case sensitive?
> I've been over the documentation a couple times now unable to find
> anything.

It's a conceptual issue; what you are looking for is not directly
related to indexes. Rather, the case sensitivity of a column (and any
key taken from there) is dependent on the type of that column. If the
type is char, then any operation on that column will by default be
case-insensitive.

This is actually noted in the manual too, when you look at the sections
on CHAR and CREATE TABLE. If you want the column to be case-sensitive,
define the column as BINARY CHAR. In your CREATE/ALTER TABLE statement:
lala VARCHAR(20) BINARY NOT NULL.

That should solve your problem 100% !


Regards,
Arjen.

-- 
Get MySQL Training Worldwide, http://www.mysql.com/training/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Technical Writer, Trainer
/_/  /_/\_, /___/\___\_\___/   Brisbane, QLD Australia
   <___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Unique index/primary key case-insensitive?

[Andreas Frøsting]

Hi there,

I'm having som difficulties trying to figure out MySQL's unique indexes
and primary keys.

Case:
mysql> create table flaf (lala varchar(20) not null, unique(lala));
  Query OK, 0 rows affected (0.00 sec)

mysql> insert into flaf VALUES ('hest');
  Query OK, 1 row affected (0.00 sec)

mysql> insert into flaf VALUES ('Hest');
  ERROR 1062: Duplicate entry 'Hest' for key 1

Here, the unique index sees "Hest" as "hest", and because of that the
second row is not inserted.

Why does MySQL have this strange behavoir, treating unique indexes
case-insensitive?
Same thing happens if I use a primary key for the column 'lala'.

Similar test in postgresql seems more logical to me:
hroi=# create table flaf(gnyf char(4) unique); 
  NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'flaf_gnyf_key' for table 'flaf' 
  CREATE 

hroi=# insert into flaf values ('hest'); 
  INSERT 2722204 1 

hroi=# insert into flaf values ('hest'); 
  ERROR:  Cannot insert a duplicate key into unique index flaf_gnyf_key 

hroi=# insert into flaf values ('Hest'); 
  INSERT 2722206 1

Can anyone explain MySQL's behaviour and how I do a workaround making
unique indexes and primary keys case sensitive?
I've been over the documentation a couple times now unable to find
anything.

regards,

//andreas
http://phpwizard.dk


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php