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
Unique Indexes across multiple columns
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
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
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
