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
