List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:June 10 2002 1:29pm
Subject:Re: Unique Indexes across multiple columns
View as plain text  
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
>
>


Thread
Unique Indexes across multiple columnsChris Knipe8 Jun
  • Re: Unique Indexes across multiple columnsJoseph Bueno8 Jun
  • Re: Unique Indexes across multiple columnsFred van Engen8 Jun
  • Re: Unique Indexes across multiple columnsGerald Clark10 Jun