List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:June 8 2002 9:44am
Subject:Unique Indexes across multiple columns
View as plain text  
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