Unique index/primary key case-insensitive?
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
Re: Unique index/primary key case-insensitive?
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
Could someone explain to me the difference between the Primary/Index/Unique attributes of MySQL? I am coming from using MS SQL where there is only the Primary key option. Sorry if this is too beginner of a question for this list. Ryan Shrout Athlonmb.com Production Manager http://www.athlonmb.com/ [EMAIL PROTECTED]
Re: Unique/Index/Primary
On Sun, Mar 18, 2001 at 03:58:46PM -0500, Ryan Shrout wrote: Could someone explain to me the difference between the Primary/Index/Unique attributes of MySQL? I am coming from using MS SQL where there is only the Primary key option. And INDEXed column may have duplicate values in it. A UNIQUE one may not. A PRIMARY KEY is simply another way to say "UNIQUE" with the side-effect that the PRIMARY KEY is stored first in the index file for the table. So UNIQUE is a special case of INDEX, and PRIMARY KEY is a special case of UNIQUE. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
