Re: Unique Indexes across multiple columns
It looks to me like 'a' is '127-30-127-1' Notice the dashes. I don't believe you are splitting the IP address into 4 separate numbers. Chris Knipe wrote: >Hi again, > >Not to long ago, I had a query regarding the best way to store IP addresses >in a DB, and make sure that they are unique. > >It was pointed out to me that I could use four smallint columns instead of a >varchar to store these numbers, and just implement a UNIQUE index across all >four columns to make sure the combination of the four columns, would never >be in duplicate. > >It seems, the UNIQUE index however still insist on having unique values for >all of the four columns... > >The DB looks like this (the four smallint columns): >NS1_IP1 smallint(5) >NS1_IP2 smallint(5) >NS1_IP3 smallint(5) >NS1_IP4 smallint(5) > >SQL-query : >ALTER TABLE `domains` ADD UNIQUE `NS1_Unique` >(`NS1_IP1`,`NS1_IP2`,`NS1_IP3`,`NS1_IP4`) > >MySQL said: >Duplicate entry '127-30-127-1' for key 2 > >Which, tells me that I can have the following: >a, b, c, d - works >b, c, d, e - works >a, b, c, d - fails >a, a, b, b - fails <-- This should however not fail... (the combination is >unique??) > >Any idea on how I can get this working?? > >-- >me > > - 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 Indexes across multiple columns
Hi Chris, On Sat, Jun 08, 2002 at 11:44:43AM +0200, Chris Knipe wrote: > Not to long ago, I had a query regarding the best way to store IP addresses > in a DB, and make sure that they are unique. > > It was pointed out to me that I could use four smallint columns instead of a > varchar to store these numbers, Why not use unsigned tinyint? This allows values 0..255 and is exactly what you need. A smallint allows -32768..32767 and use two bytes i.s.o. one. See http://www.mysql.com/doc/C/o/Column_types.html for more info on column types. By the way, I just use unsigned int for my IP adresses, and INET_NTOA for text representation. > and just implement a UNIQUE index across all > four columns to make sure the combination of the four columns, would never > be in duplicate. > Seems ok. > It seems, the UNIQUE index however still insist on having unique values for > all of the four columns... > > MySQL said: > Duplicate entry '127-30-127-1' for key 2 > Did you check with a SELECT if there really is a duplicate 127.30.127.1 ? Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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 Indexes across multiple columns
Chris Knipe wrote : > > Hi again, > > Not to long ago, I had a query regarding the best way to store IP addresses > in a DB, and make sure that they are unique. > > It was pointed out to me that I could use four smallint columns instead of a > varchar to store these numbers, and just implement a UNIQUE index across all > four columns to make sure the combination of the four columns, would never > be in duplicate. > > It seems, the UNIQUE index however still insist on having unique values for > all of the four columns... > > The DB looks like this (the four smallint columns): > NS1_IP1 smallint(5) > NS1_IP2 smallint(5) > NS1_IP3 smallint(5) > NS1_IP4 smallint(5) > > SQL-query : > ALTER TABLE `domains` ADD UNIQUE `NS1_Unique` > (`NS1_IP1`,`NS1_IP2`,`NS1_IP3`,`NS1_IP4`) > > MySQL said: > Duplicate entry '127-30-127-1' for key 2 > > Which, tells me that I can have the following: > a, b, c, d - works > b, c, d, e - works > a, b, c, d - fails > a, a, b, b - fails <-- This should however not fail... (the combination is > unique??) > > Any idea on how I can get this working?? > > -- > me > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > Hi, I tried it and it works for me: mysql> create table domains ( -> NS1_IP1 smallint(5), -> NS1_IP2 smallint(5), -> NS1_IP3 smallint(5), -> NS1_IP4 smallint(5) -> ); Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `domains` ADD UNIQUE `NS1_Unique` -> (`NS1_IP1`,`NS1_IP2`,`NS1_IP3`,`NS1_IP4`); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO domains VALUES (1, 2, 3, 4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO domains VALUES (2, 3, 4, 5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO domains VALUES (1, 2, 3, 4); ERROR 1062: Duplicate entry '1-2-3-4' for key 1 mysql> INSERT INTO domains VALUES (1, 1, 2, 2); Query OK, 1 row affected (0.00 sec) mysql> select * from domains; +-+-+-+-+ | NS1_IP1 | NS1_IP2 | NS1_IP3 | NS1_IP4 | +-+-+-+-+ | 1 | 1 | 2 | 2 | | 1 | 2 | 3 | 4 | | 2 | 3 | 4 | 5 | +-+-+-+-+ 3 rows in set (0.04 sec) Since MySQL is telling you "Duplicate entry '127-30-127-1' for key 2", are you sure you don't have another unique index defined ? Could you show us your exact table definition (SHOW CREATE TABLE domains) ? Regards -- Joseph Bueno NetClub/Trader.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
