Is it necessary to have a primary key ID with Auto Increment if I have a UNIQUE field INT?

I want to store in a table a lis开发者_如何学Got 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.

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.

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.

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.