2

I want to store in a table a list of IP ADDRESS to check later if some IP is already used in my system.

I want to store the ip in longip mode (signed int). And since each IP is unique i want to know if is necessary to have a primery key field (id, with autoincrement) or if its okey (and better) to just use my longip field as primary key.

dplante
  • 2,445
  • 3
  • 21
  • 27
Arnold Roa
  • 7,335
  • 5
  • 50
  • 69

3 Answers3

3

if in the future you have to use the key for joining it with another table, the other table should contain all the number, and that's a lot of space wasted.

for example, you have a "computer" table

in that table, you have computes with ip's. For joining you need a key right? so, if you join by key, you should have the computer id and the key (in this case the ip)

I higly recommend to use a simpler id with autoincrement, like it's beeing done since mainframe (as400), iSeries, etc.

Eric Frick
  • 847
  • 1
  • 7
  • 18
1

I think Marc_s' answer to the question When not to use surrogate primary keys? can guide us

I would say the following criteria must be met:

  • your natural key must be absolutely, positively, no-exceptions-allowed, unique (things like names, social security numbers etc. usually seem to be unique - but really aren't)

  • your natural key should be as small as an INT, e.g. not significantly more than 4 bytes in size (don't use a VARCHAR(50) for your PK, and especially not for your clustering key in SQL Server!)

  • your natural key ought to be stable, e.g. never change (OK, with ISO country codes, this is almost a given - except when countries like Yugoslavia or the USSR collapse, or other like the two Germanies unite - but that's rare enough)

If those conditions are met, you can consider a natural key as your PK - but that should be the 2% exception in all your tables - not the norm.

So I would say you should probably use a surrogate primary key. You can always use IP as a unique key if you want to

Since you're using the longip which as you pointed out is probably ok to use it.

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
1

Almost every time you start out with a natural key, you will end up regretting it later. Something will happen happen, its Murphy's law. Spare yourself the trouble, just add the ID column.

If its the auto-increment you don't like just use a uuid. MySql has a uuid function to make that easy.

stevenf
  • 166
  • 5