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
